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

Just few Manual Step..

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.

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

Go to the data tab and select text to coulm to seperate the data into multiple pieces

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

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

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

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

“

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

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

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

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

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

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

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

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.

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.

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?

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.

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

Try Find & Replace: Find AM and replace with AM but add a space in front. But select only that column when you do it.

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.

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

I would like to have the numbers 000000000 to be as 0 000 000 000 using formula.pls help.

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

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

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

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

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

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

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

STRING NEED

ABCD12BB = ABCD 12 BB

12ABCD4 = 12 ABCD 4

QB2CB = QB 2 CB

add space between characters Only

Like amed to a m e d ??!!