trim function?

[tizwaz]

I have a set of mobile numbers input in the format

11111 111 111

and I need it to become in the format

11111 111111 (ie remove the 2nd space)

I'm sure there is a formula that will do this but don't know how to word it?

 

[SkipVought]

Hi,

Use the Left and Right string functions to concatenate a new string with the new format.
[tt]
=Left(A1,9)&Right(A1,3)
[/tt]
assuming that they all have the same format.

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[bkpchs237]

tizwaz,
Are your numbers actually numbers when brought in? By this I mean in the formula bar is the "number" appearing as 11111111111 (eleven 1's) or is it some sort of text entry? Also, do you want to keep them as numbers?
If yes and yes, then the custom format for the cells as it appears presently thru Format, Cells, Custom would be "00000 000 000" (without the quotes). To change the format to the one you desire set a custom format of "00000 000000".
If not then Skip's concatenation formula is a winner if your strings are exactly 13 characters long. If they vary in length, but have a space as the 4th to last character, then this general formula should suffice:
=LEFT(A1,LEN(A1)-4)&RIGHT(A1,3)

Hope this helps.

 

[byundt]

The SUBSTITUTE function allows you to specify which instance of a string gets replaced:
=SUBSTITUTE(A1," ","",2) deletes the second space character

Brad