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