Add space between characters and numbers in Microsoft Excel

by  About
       

Lets say.. Data is in below format.

Add8612875835
space1696927498
between930042083
characters4630303906
and3658091258
numbers9947236542
in634569341
Microsoft6085349775
Excel1847152113

So, if we need only the first part with Text, we can use Some array Formula or some long formula to track 1st Numeric and 1st Text, then according return, we can use Concat both Returns..

Excel 2013, make all these concept, within a simple Flash Fill.

You just have to type the pattern to get the data.

If you need only Number or only text, you can do this by simple manual input.

take a look at the bottom screen, No Code, No Formula, only Excel 2013, Flash Fill..

Flash Fill Add Space

 

Just few Manual Step..



12 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

Leave a Reply

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


5 − = two

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>