Can Excel Ignore letters after Numbers when Calculating

danjo-xx · 16-10-2010 10:33pm #1

Take ESB bill where units might read 23445E or 43564C e being estimated and c being customer. When I put a letter after a number in Excel it corrupts the calculation.

I'm sure there must be a way round this:(

ajc100 · 17-10-2010 12:37am

Run a macro to copy them to another sheet, delete or separate the letter's, do the calculation on the numbers and return the result to the original sheet.

William Powell · 17-10-2010 6:46pm

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Where A1 is replaced with the cell that has the numeric data and text.

So you put your data into one column and then convert it to numeric data in the next column with the above code. Obviously you do your calculations in the second numeric only column.

- started to work it out but looked it up in the end and found some good info here

danjo-xx · 17-10-2010 9:07pm

OH MY GOD I'm sorry I asked

I'm toooo old for all that effort:( but appreciate your replies.

William Powell · 17-10-2010 11:43pm

danjo-xx wrote: »

OH MY GOD I'm sorry I asked I'm toooo old for all that effort:( but appreciate your replies.

It was a good question, that I thought must have an easy answer, but I was wrong and had then to find out for myself so got halfway through working out a solution and decided life was too short so just googled for an answer.

Its not really that tricky. Put the first number (with E or whatever on the end) in cell A1 then the code in cell B1, the result of the code will be the number without the letter. If you put your next reading in the in cell A2 then you should be able to grab the corner of the cell B1 and pull it down to cell B2 which will copy the code but change the reference in it from A1 to A2.

danjo-xx · 18-10-2010 8:42pm

ts not really that tricky. Put the first number (with E or whatever on the end) in cell A1 then the code in cell B1, the result of the code will be the number without the letter. If you put your next reading in the in cell A2 then you should be able to grab the corner of the cell B1 and pull it down to cell B2 which will copy the code but change the reference in it from A1 to A2.

Now that sounds more up my street. Thanks a mill.

Can Excel Ignore letters after Numbers when Calculating

Comments