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.

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.


"Zak" wrote:

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.