Add space between characters and numbers in Microsoft Excel





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..

 

 

 

 

 

 

 



21 thoughts on “Add space between characters and numbers in Microsoft Excel

  1. “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”””

  2. “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.”

  3. “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.”

  4. “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?”

  5. “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.

  6. “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”””

  7. “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.

  8. “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. “

  9. “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? “

  10. “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?”

  11. “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. “

  12. 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

  13. 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.

  14. 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.

  15. 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?

  16. 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.

  17. 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

  18. 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.

  19. 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

Leave a Reply

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


5 + one =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>