How do I extract the numbers-only or letters-only portion of a cell value?

[Auseklis]

I need to regularly process data files to ready them for data importation and I have fields where the numbers and units are already concatenated. i.e., “50LB”, “15OZ”, “BLOCK”, “TON”, “15g”

For Example:

Where $Sheet1.B3 = “50LB”. I’d like to use a formula to have $Sheet2.B3 = “50” and $Sheet2.B4 = “LB”

Further, I need the forumla for $Sheet2.B3 set the value to “1” when no numbers were found in $Sheet1.B3.

Thank you in advance for any help or advice.

[anon73440385]

Hello,

for your example use in:

• cell $Sheet2.B3: =IFERROR(REGEX($Sheet1.B3;"^[:digit:]+");1)
• cell $Sheet2.B4: =REGEX($Sheet1.B3;"[^[:digit:]]+")

Note

The formulas assume the data to be ##text (i.e. number always before text; there is no text##). If this is also possible, leave a comment.

Hope that helps.

If the answer works for you, please consider to click the check mark () next to the answer. Thanks in advance …

[Auseklis]

Thank you! Works perfectly!

[JohnSUN]

Honestly, I don’t like this solution, sooner or later it will fail. But, perhaps, this is enough for your tasks.

Just try

=REGEX(B3;"[0-9]";"";"g")
=SUBSTITUTE(B3;D3;"")