Add space between characters and numbers in Microsoft Excel





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

Arlette Aloysius

Arlette Aloysius has more than 10 years of experience working with excel. She started teaching excel and other microsoft office programs to government employees when she was in college as part of extra-curricular activities. In her 9 years of work experience, she has worked extensively on excel and automated several reports using vba. In her current role as administrator of Excelforum.com, she provides simple as well as complicated vba solutions to users who need their reports to be automated. She is a Commerce graduate who specialized in Computer Applications where she was able to pick up the basics of the VB programming language. She then pursued to do her Post Graduate Diploma in International Business. Excel sheets are part of her daily life. She uses it even for small personal uses like keeping track of household expenses, investments, etc. By using excel as a tracker cum dashboard helps her to be up-to-date with all financial aspects in her day to day operations.



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

Leave a Reply

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


+ nine = 18

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>