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!

[TABLE="width: 51"]
<tbody>[TR]
[TD]2.5Z[/TD]
[/TR]
[TR]
[TD]2.25Z[/TD]
[/TR]
[TR]
[TD]2Z[/TD]
[/TR]
[TR]
[TD]2Z[/TD]
[/TR]
[TR]
[TD]57G[/TD]
[/TR]
[TR]
[TD]2Z[/TD]
[/TR]
[TR]
[TD]2Z[/TD]
[/TR]
[TR]
[TD]2Z[/TD]
[/TR]
[TR]
[TD]6UN[/TD]
[/TR]
[TR]
[TD]2Z[/TD]
[/TR]
[TR]
[TD]50UN[/TD]
[/TR]
[TR]
[TD]50UN[/TD]
[/TR]
[TR]
[TD]10L[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 51"]
<tbody>[TR]
[TD]2.27KG[/TD]
[/TR]
[TR]
[TD]1L[/TD]
[/TR]
[TR]
[TD]2.5KG[/TD]
[/TR]
[TR]
[TD]2KG[/TD]
[/TR]
[TR]
[TD]1.7KGA[/TD]
[/TR]
[TR]
[TD]3KG[/TD]
[/TR]
[TR]
[TD]2KG[/TD]
[/TR]
[TR]
[TD].650GA[/TD]
[/TR]
[TR]
[TD]125G[/TD]
[/TR]
[TR]
[TD]500G[/TD]
[/TR]
[TR]
[TD]1.5KGA[/TD]
[/TR]
[TR]
[TD]125G[/TD]
[/TR]
[TR]
[TD]2.8KGA[/TD]
[/TR]
[TR]
[TD]2.27KG[/TD]
[/TR]
[TR]
[TD]21G[/TD]
[/TR]
[TR]
[TD]4.54KG[/TD]
[/TR]
[TR]
[TD]2.27KG[/TD]
[/TR]
[TR]
[TD]2.5KG[/TD]
[/TR]
[TR]
[TD]2.5KG[/TD]
[/TR]
[TR]
[TD]2.27KG[/TD]
[/TR]
</tbody>[/TABLE]

 

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