Want to do opposite of concatenate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to take a column with numbers and letters, like Brad2, Sharon15, Pat3 and separate into 2 columns. One column with the text and the 2nd column with the numbers. So it would be Brad, Sharon, Pat in column 1 and 2, 15, 3 in column 2.
 
Hi Cricket1001!

If all your numbers are 1 or 2 digits then the following will do the
job but others might have better suggestions.

I have the concatenated names in A1 downwards
In C1 I have the formula:

=IF(ISNUMBER(--RIGHT(A1,2)),--RIGHT(A1,2),--RIGHT(A1,1))
Copied down

In B I have the formula:
=LEFT(A1,LEN(A1)-LEN(C1))
Copied down

It seems to work OK on the examples given.

If you have some without numbers and / or some with more digits, then
you will need to revise but the principle works OK.

You might note that we use the -- approach to force the evaluation of
a string as a number (it's the same as multiplying twice by -1). An
alternative is to use the VALUE function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
cricket1001 said:
I want to take a column with numbers and letters, like Brad2,
Click to expand...
Sharon15, Pat3 and separate into 2 columns. One column with the text
and the 2nd column with the numbers. So it would be Brad, Sharon, Pat
in column 1 and 2, 15, 3 in column 2.
 
Norman
Thank you so very, very much. That worked perfectly. Please have a great Holiday Season and take care!!! :)
 
Hi Cricket1001!

That's OK as long as you're supporting Australia or Essex!

Always good to hear it works OK.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
cricket1001 said:
Norman,
Thank you so very, very much. That worked perfectly. Please have a
Click to expand...
great Holiday Season and take care!!! :)
 
Back
Top