Separate letters from numbers

[cecirue]

Hi! I'm new to this board and I'm sure this is a very simple solution however I'm stuck right now.
I have a column that looks like the below however consistently changes in number of characters and letter. I need to be able to separate the numbers from the letters as they are the unit of measurement. I believe the mid or right function would work together however I just can't figure it out. Any help would be greatly appreciated. I would love to be able to isolate both the numbers and letters into their own cells.

Thanks so much!

2.5Z

2.25Z

2Z

2Z

57G

2Z

2Z

2Z

6UN

2Z

50UN

50UN

10L

<tbody>
</tbody>

 

[Joe4]

Welcome to the Board!

Are there always exactly only one or two letters at the end, or are there also other variations?

 

[cecirue]

always variable. There is no consistency. Here's another batch from it. The most # of letters is 4 letters and the least is 1. As for numbers they vary as well.

2.27KG

1L

2.5KG

2KG

1.7KGA

3KG

2KG

.650GA

125G

500G

1.5KGA

125G

2.8KGA

2.27KG

21G

4.54KG

2.27KG

2.5KG

2.5KG

2.27KG

<tbody>
</tbody>

 

[Joe4]

Personally, I would be inclined to use VBA and create a User Defined function, as if you have that uncertainty, the formulas can get a little messy.
Here are some past threads that show both formulaic and VBA solutions.
http://www.mrexcel.com/forum/excel-questions/463005-extract-number-alphanumeric-string.html
http://www.mrexcel.com/forum/excel-...ext-middle-alphanumeric-string-up-number.html
http://www.mrexcel.com/forum/excel-questions/786066-formula-extract-numbers-alphanumeric-string.html

 

[cecirue]

Thanks Joe4! I feared the idea that VBA was the safest way to get the answer to this. I was trying my hardest not to go that route.

 

[Joe4]

Maybe you can try to incorporate some formulas like Barry Houdini did here in this example: http://www.mrexcel.com/forum/excel-questions/410138-extract-number-text-cell.html

 

[István Hirsch]

Try this, enter in

B1:

=-LOOKUP(0,-LEFT(A1,{1,2,3,4,5,6,7,8,9}))

C1:

=SUBSTITUTE(A1,B1,"",1)

and copy down.