Separating cell values based on numbers, letters, and symbols

[sungraph]

Hi everyone,
I have a spreadsheet that contains a column of data that is a mixture of letters, numbers and symbols all based on the last row of the keyboard. The data looks like this:

23C\v
5B\n
12<\.
23Z?\zc

This data is used to create whole numbers and fractions when exported to another program. (The numbers represent the whole number and the case-sensitive letters and symbols represent the numerators and denominators.)

I need to be able to separate the numerical portion of the cells from the rest by adding a space between the numbers and everything else. (23Z?\zc will become 23 Z?\zc) The backslash will always be included, but there could be one or two numbers or symbols before and after the backslash. The characters before the backslash can include the following characters between the quote marks: "ZXCVBNM<>?" The characters after the backslash can include: "zxcvbnm,./"

Is there any formula that can be used to separate this data or will I have to record macros searching for the various combinations?

Any help is much appreciated!

 

[Andrew Poulsom]

If there can't be more than 2 numbers at the beginning try:

=SUBSTITUTE(A1,MID(A1,1,1+(ISNUMBER(MID(A1,2,1)+0))),MID(A1,1,1+(ISNUMBER(MID(A1,2,1)+0)))&" ")

 

[sungraph]

Thank you so much! This is wonderful and will fix almost everything. Could it be modified to work if there are 3 numbers at the beginning?

 

[pgc01]

Hi sungraph

For up to 3 digits at the beggining try:

=LOOKUP(1000,--LEFT(A1,{1,2,3}))

 

[Andrew Poulsom]

Using pgc01's clever formula, my formula would be:

=SUBSTITUTE(A1,LOOKUP(1000,--LEFT(A1,{1,2,3})),LOOKUP(1000,--LEFT(A1,{1,2,3}))&" ")

 

[sungraph]

That works great. Thank you both so much for your help!

 

[pgc01]

Hi sungraph

Now I see that I did not read your OP with attention. I had understood it was just to extract the number and not to insert a space between the number and the rest.

I see Andrew posted already a solution, here's another:

=REPLACE(A1,LOOKUP(1000,--LEFT(A1,{1,2,3}),{2,3,4}),0," ")