adding cells with numbers & letters in

[scarecrow55]

Can Excel add cells with numbers & letters in.
e.g 41 in one cell and DF42 in another and get the total to 83? I don't want to remove the letters there is too many cells in total.
Thanks

 

[FrenchCAD]

I'm affraid not...Maybe you could write a macro to remove letters from cells

Cyril Guichard
Mechanical Engineer Consultant
France

 

[joerd]

ASAP utilities has, among a great number of other tools, the option to delete all text characters in a cell. It is free, so why not give it a try? ;-) You can download it from

http://www.asap-utilities.com/

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Clyde38]

If the number of letters is the same for all cases, you could use the =value() statement after you have subtracted the letters. Example:
=VALUE(MID(G10,3,100)) assumes DF42 is in cell G10 and the max mumber of characters for the number (42) would be 100.

 

[FrenchCAD]

this only works if you have 2 letters in your cells though...

Cyril Guichard
Mechanical Engineer Consultant
France

 

[Clyde38]

True, however if you change the 3 in =VALUE(MID(G10,3,100)) (starting position for the mid function) you can have more or less letters. Of course you could use a conditional statement =IF() to determine how many letters are in the cell and avoid this limitation. I would opt for the easiest approach.

 

[smah]

You can also use the Left & Righ functions as needed.

Steve
Eichenauer, Inc.

 

[electricpete]

Let's say you want to test the 4th character. Use:
=+ISNUMBER(VALUE(MID(I15,4,1)))

Returns true if it's a digit, false otherwise.

This can be part of a nested if statement.

Can the original poster clarify what are the limits of the types of data that might be seen? Letters only on the left or possibly in the middle also? Numbers only on the right? Max number of letter or numbers or both?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[scarecrow55]

Thanks, the cells could be either QWE123456 or QWE12345.

 

[electricpete]

If you always have 3 letters to remove from the front, you can do it as follows (assuming your QWE123456 is in cell H15)

=+VALUE(RIGHT(H15,LEN(H15)-3))

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Just to clarify: len determines total length of the string, len-3 would be number of digits at the end. The right function returns those digits on the end, and the value function converts it to a number

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[scarecrow55]

Thanks guys - sorted, just shows the power of Eng-Tips

 

[joerd]

electricpete,
a shorthand way to do what you intend, and maybe easier to understand, is to use [tt]=MID(H15, 3, 999)[/tt] where the 999 is just a placeholder big number, since Excel wants something there. You should use a number which is at least as big as the longest string length.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

I think you meant
=MID(H15, 4, 999)

Easy to read is in the eye of the beholder. Mine says I'm taking everything but the first 3. Yours says your starting at the 4th and taking the next 999.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

But good to see alternative approaches. Yours is more economical.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

And now I see Clyde38 essentially gave the same answer. Call that economical...

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IJR]

Doesnt custom format do this?

Forgot now but used to type in 45 and see 45USD sometimes,with the "USD" done automatically.

regs
IJR

 

[joerd]

IJR,

Actually, using a custom format is the other way around - and much easier for Excel. If you format a cell as [tt]General "USD"[/tt], and you type 34 into the cell, Excel will display 34 USD. However, the cell contents are 34, which is a number, so you can do calculations with it. In this case, it looked as if scarecrow55 has imported data that contains letters and numbers. Then, you have to parse the data to get numbers for Excel to work with. After all, it is just a beefed up calculator...

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.