Add space between characters and numbers in Microsoft Excel 2013

In this article we will learn how to add space between character and numbers in Microsoft Excel 2010.

To understand how to add space between characters and numbers in Excel, we use the below mentioned Data, which is in alpha numeric format. Lets say.. Data is in below format.

 

Add8612875835
space1696927498
between930042083
characters4630303906
and3658091258
numbers9947236542
in634569341
Microsoft6085349775
Excel1847152113

 

If we need only the first part with Text, we can use Array Formula or any other long formula to track 1st Numeric and 1st Text. Initially, we used to use formulas to get the result, but now in excel there is a new concept which is known as Flash Fill. Excel 2013, make all these concept, within a simple Flash Fill.

 

In the cell, start typing the pattern to get the data.If you need only Number or only Text, you can do this through simple manual input. Write just the numbers and automatically you can see the format in below cells. If you type alphabets then you can see the alphabet format in below cells.

 

This way you can select Flash Fill. take a look at the bottom screen, No Code, No Formula, only Excel 2013, Flash Fill..

 

 

Flash Fill Add Space  Just few Manual Step..

 

 

 

 

 

 

 

Comments

  1. Can anyone help me with a formula? I have an 11 digit number like this 12345678912 and I need it split to be 12 345 678 912.

    Thanks 🙂

  2. Bhagavata Gananadhyayi

    hi friends,
    i am using Unicode text stings in Excel 2010. i need a space between each Unicode letter. can please help me. Example:
    input cell "Sri Rama" - - -out put cell "S r i R a m a" (in English)
    input cell "???? ???" - - -out put cell "????i ?? ?" (in Telugu Language)
    input cell "213 mumbayi" - - -out put cell "2 1 3 m u m b y i" (in English)
    input cell "213 ???????" - - -out put cell "2 1 3 ??? ?? ??" (in Telugu Language)

    note: even if extra spaces added no problem for me

    • Ramandeep Singh

      Hi Bhagavata,

      You can use the below given user defined function for getting the required output.
      Function CharacterSpacing(UserInput As String)
      Dim NumberOfCharacters, i As Long
      NumberOfCharacters = Len(UserInput)
      For i = 1 To NumberOfCharacters
      CharacterSpacing = CharacterSpacing & Mid(UserInput, i, 1) & " "
      Next

      End Function

  3. I have a Formula which calculated Y, M, D and displays it as x"years" x "months" x "dates", I would like to make 3 columsn as YR MntH Days and display only the numbers that fit into this columns, my problem is I can not adjust the gap between the display

    The formula i am using is =DATEDIF(TODAY(),L2,"y")& " Years "&DATEDIF(TODAY(),L2,"ym")& " Months "&DATEDIF(TODAY(),L2,"md")& " Days"

    how can i adjust the display info in the formula, I have tried increasing distances by using spacebar but not very successfully.

    Thank you very much for your help in advance.. Regards

  4. Hi,

    I am using Excel 2007.

    I need to add space in a text string in a manner that is separates characters from numbers. For eg.

    Existing data:

    ABCD1234XYZ
    AB12XY
    ABCDE12345LMNOP
    ABCDE10-1UVW

    Required data:

    ABCD 1234 XYZ
    AB 12 XY
    ABCDE 12345 LMNOP
    ABCDE 10-1 UVW

  5. please every one can tell me how to add space between numbers in one cell for example in below numbers.
    2330031160
    1780151020
    3121060300
    3123060260

    • Where exactly do you want to add space? After every digit or is there a certain format in which you want to add space. Also let us know the version of excel you are using

    • If your number values are limited to 10 digits then you can use the text function.

      Use the below formula;

      =TEXT(B2,"0 000 000 000")

      Your number will be converted to text and can be changed back to numbers by using numbervalue function

      Regards,
      Tarun

  6. Hi all,

    I have a similar situation; I need to add a space.
    CCLNL2525M12 to CCLNL 2525 M12

    Any help would be appreciated.

    Thanks
    Dave

  7. I have a similar request. However, mine is with all text. I have a list of over 3000 names of companies and a lot of them are stuck together. Example: CUPOFJOECOFFEEHOUSE should be CUP OF JOE COFFEE HOUSE. Does anyone have a formula or a trick that would separate the words (can be within the same cell or separated into different cells).

    Any help with this is greatly appreciated.

    Thanks.

  8. i have some data from CMS ... when i pulled data from CMS time comes like this ( 7:00AM ) so every time i have manually replace with ( space ) so then it come like this ( 7:00 AM ) which is the correct format for time

    now i what i want is by doing manually every time can we fixed formula so that we can put space between this.

    Note:- i tried doing =right but time which is 7:00AM, 11:30AM, 12:00PM, 02:00PM so count of character is keep changed as per time

    please let me know if you have solution for this

    thanks

    kamran shaikh

  9. friends i have a problems.

    i have a very critical data numeric and alphabet values are coming in an excel cell. And i just want to take only numberic values from that excel cells.
    .For example my data having something like this (Value_249_Per_Min_Plan_OGS ,Advance 399 Per Min Plan_OGS ,Standard_124 Plan_HAR ,Value_199_Per_Sec_Plan_OGS)

    i need a simple excel formula if possible.

  10. I am attempting to do something Similar.

    I am trying to separate one word in one cell into two words into another cell.

    Ex. Cell 1: 6:00PM Cell 2: 6:00 PM

    Thank you

    • I need to do the same thing. I have data from reception with some time entries written as 9:00am and need it to be 9:00 AM, so need to add a space before the character from the right... Seems like a pretty simple thing do to. Of course, some of the values have two characters before the colon, while others have one (e.g. 12:00pm vs 9:00am).

      Any insight on this?

  11. if I want to put 7-3 or 8-5 or something like that in the box without it changing it to a date or something else how do I do that.

  12. HI

    I have a similar requirement, however what I want to do is use a formula to calculate the Len of cell A1, and then see the difference from total characters string should be (i.e. 16 Chars) and add "_" to make up the missing character. Not sure if this is clear, here is an example

    A1 = "Ball" therefore len = 4,
    hence B1 = should be "Ball____________" i.e. 4+12
    A2 = "Garden" therefore len = 6
    hence B2 = should be "Garden__________" i.e. 6+10

    Any ideas how I can use a formula to insert the a variable number of character(s) in a string without actual VBA function.

  13. Hello I'm using a similar process where I need to remove text from 2 columns, add some text and than combine both cells together for one long string. Here's an example.

    Cell A2 - user_mention:(Apple)
    Cell B2 - hashtag:(Apple AppleCare)

    I need to remove the colons, brackets and the words user_mention and hashtag. Than I need to add @ and # symbols to EACH term and separate each term with a comma space.

    The final result would look like this.
    Cell C3 - @Apple, #Apple, #AppleCare

    I've gotten close to accomplishing this. I've run into a problem when the "user_mention" cell does not exist, and the "hashtag" cell does exist. I'll get a #VALUE response.

    If you have any ideas, I'd be very grateful to hear them.

    Thanks,
    Eric

  14. "No problem!

    To reverse engineer, you can just work your way in through the brackets.

    You need to understand arrays first though (if you don't already).

    Sorry - no idea on MS Map. I never used it. "

  15. "Appreciate that. All I need to do now is reverse engineer and figure out how the hell it works 🙂

    Gareth

    PS I don't suppose you know any useful resources for MS MAP & Excel do you?"

  16. "If the post code is in cell A1, try this:

    {=MID(A1,1,MATCH(FALSE,ISERR(VALUE(MID(A1,ROW(INDIRECT(""1:""&LEN(A1))),1))),0)-1)}

    Note that this is an array formula, and must be entered using Shift-Ctrl-Enter.

    Does that work for you? "

  17. "I am looking to do something similar. I am using MS Map to create some ""pretty"" reports based on the post code for our customers. Here is the query:

    CF46 5PL
    M11 2NE
    B14 7SG
    RH13 4RT

    The above is a sample of possible post codes within my records. I want to extract the first letter(s) from the post code i.e. RH13 4RT = RH, B14 7SG = B
    I want all the letters up until the first number appears (post code area).
    Any suggestions would be appreciated, or an alternative within MS MAP that I may not be aware of. "

  18. "paste ""=LEFT(C10,FIND(""*"",SUBSTITUTE(C10,"" "",""*"",LEN(C10)-LEN(SUBSTITUTE(C10,"" "",""""))))-1) "" for displaying first name & middle name in one cell & paste ""=RIGHT(C10,LEN(C10)-FIND(""*"",SUBSTITUTE(C10,"" "",""*"",LEN(C10)-LEN(SUBSTITUTE(C10,"" "","""")))))"" in another cell for displaying the last name.

  19. "could you tell me how to separate the full text in one cell into two cell
    eg.
    Lam Chi Vinh
    into
    ""Lam Chi "" and other cell contain ""Vinh"""

  20. "Hi Gareth,

    No problem!

    To reverse engineer, you can just work your way in through the brackets.

    You need to understand arrays first though (if you don't already).

    Sorry - no idea on MS Map. I never used it.

    Alan.
    "

  21. "Alan, thats fantastic !
    Appreciate that. All I need to do now is reverse engineer and figure out how the hell it works 🙂

    Gareth

    PS I don't suppose you know any useful resources for MS MAP & Excel do you?"

  22. "Hi Gareth,

    If the post code is in cell A1, try this:

    {=MID(A1,1,MATCH(FALSE,ISERR(VALUE(MID(A1,ROW(INDIRECT(""1:""&LEN(A1))),1))),0)-1)}

    Note that this is an array formula, and must be entered using Shift-Ctrl-Enter.

    Does that work for you?

    Alan."

  23. "I am looking to do something similar. I am using MS Map to create some ""pretty"" reports based on the post code for our customers. Here is the query:

    CF46 5PL
    M11 2NE
    B14 7SG
    RH13 4RT

    The above is a sample of possible post codes within my records. I want to extract the first letter(s) from the post code i.e. RH13 4RT = RH, B14 7SG = B
    I want all the letters up until the first number appears (post code area).
    Any suggestions would be appreciated, or an alternative within MS MAP that I may not be aware of.
    Thanks."

    • Not on MS MAP but in MS Excel, this is easy to pull.
      Assuming that you have your text in A2. Write this formula in any cell to extract text before any number appears.

      =LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

  24. "could you tell me how to separate the full text in one cell into two cell
    eg.
    Lam Chi Vinh
    into
    ""Lam Chi "" and other cell contain ""Vinh"""

    • Please update your name in cell "A1 & use this formula in cell B1 =RIGHT( A1, LEN( A1 ) - FIND( " ", A1 ) ) & use this formula in C1 =RIGHT( B1, LEN( B1 ) - FIND( " ",B1 ) ) now you got the answer

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.