How to delete numeric characters in a cell with alpha numeric char

Z

Zak

Hi,

I have a spreadsheet populated with the following type of data:

xyz,"90,500","110,000"

xyz2,"33,100","18,100"

Its 200 cells and all have a mix of the above characters, it was imported
from a system so appears like this. I need to tidy up the data so it removes
everything besides the letters i.e. xyz should remain but all the numbers and
special characters like " " and , should be removed.

Dont think text2columns will work or the =LEN formula.

Any help would be greatly appreciated.

Thank you.
 
M

Mike H

Hi,

From the examples you posted this would work

=LEFT(A1,3)

But i doubt it's as simple as that so if the length of the leading text can
vary try this

=SUBSTITUTE(A1,MID(A1,MIN(FIND({",",0,1,2,3,4,5,6,7,8,9},A1&",0123456789")),LEN(A1)),"")

Drag both down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete row if cell in column "E" has 1 or more alpha characters 3
Formula to remove all non-numeric characters from a cell 1
Identifying Numeric or Alpha characters in a table 1
How to find row with more than 255 characters in a cell 4
remedy: import problems with alpha and numeric data in column 3
Problem with counting characters in a cell 2
Importing text Field from Excel - Numeric characters appear as numbers in scientific format - Access 1
Can you ID a cell that has both Alpha AND Numeric characters? 5

Top