Extract Text and numbers from mixed cells

**Crawfy** · 11-25-2017, 03:02 AM

I have a column of cells with alphanumeric data. I would like to separate the text into one column and the numbers into another.
The data is mostly text first followed by numbers. (eg ABCDE123)
Occasionally there will be more letters or hyphens following the numbers. (eg ABCD1234-I)

I am currently using the following formulas...
To separate text: (I am happy with the results this produces)

Formula:

Please Login or Register  to view this content.

To separate numbers: (This is my problem area)

Formula:

Please Login or Register  to view this content.

These work fine until the hyphen I (eg. -I) follows the data. Both the numbers and -I are displayed but I only want the numbers. I do not want to display the -I in either destination cell.

Any suggestions on how to display numbers only whether they are at the end of the data or surrounded by text?
Thanks

**shukla.ankur281190** · 11-25-2017, 03:09 AM

Try

for numbers

Formula:

Please Login or Register  to view this content.

**samba_ravi** · 11-25-2017, 03:13 AM

try=LOOKUP(99^9,MID(A6,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A6&1/19)),{1,2,3,4,5,6,7,8,9,10})+0)
to get number

**Crawfy** · 11-25-2017, 03:25 AM

To shukla.ankur281190 and nflsales
Thanks, this does not add the [B]-I[B] which is great.
However, if there are 0's in the numbers (eg, ABCD002) they are not included and the result is (eg). 2 not 002.
Any suggestions on how to retain the 0s? If the numbers are of varying lengths I would like to keep the correct number of leading zeros.
I know how to customize a cells formatting to retain a set number of digits, but not if they are of varying length.

**samba_ravi** · 11-25-2017, 03:44 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Crawfy** · 11-25-2017, 05:06 AM

From the Tab "Device List" I have used your formula in cells D6-10. This outputs numbers without leading zeros from the data entered in cells A6-10.
In cells D12-16 I have change the formatting to text and manually typed the output i would expect to see from the data entered in cells A12-16
I hope that makes sense.

**shukla.ankur281190** · 11-25-2017, 05:12 AM

try

D6

Formula:

Please Login or Register  to view this content.

Copy down

**Crawfy** · 11-25-2017, 05:36 AM

Thats great thank you.
I'll have to study up on the trim and substitute functions.

Extract Text and numbers from mixed cells

LinkBack

Thread Tools

Rate This Thread

Display

Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Re: Extract Text and numbers from mixed cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract value left of priod in mixed text and number

[SOLVED] Formula to extract text only from mixed string

Extract Numbers in Mixed Cell (not string)

how to extract the nos from the text (text and nos mixed inbetween) with formulas in excel

Extract Numbers from Mixed (Text + Numeric) Data

Extract text from cells with mixed characters

Summation of cells with mixed numbers & text

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions