×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

adding cells with numbers & letters in

adding cells with numbers & letters in

adding cells with numbers & letters in

(OP)
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

RE: adding cells with numbers & letters in

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

Cyril Guichard
Mechanical Engineer Consultant
France

RE: adding cells with numbers & letters in

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? winky smile 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.

RE: adding cells with numbers & letters in

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.

RE: adding cells with numbers & letters in

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

Cyril Guichard
Mechanical Engineer Consultant
France

RE: adding cells with numbers & letters in

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.

RE: adding cells with numbers & letters in

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

Steve
Eichenauer, Inc.

RE: adding cells with numbers & letters in

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.

RE: adding cells with numbers & letters in

(OP)
Thanks, the cells could be either QWE123456  or QWE12345.

RE: adding cells with numbers & letters in

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.

RE: adding cells with numbers & letters in

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.

RE: adding cells with numbers & letters in

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

RE: adding cells with numbers & letters in

electricpete,
a shorthand way to do what you intend, and maybe easier to understand, is to use =MID(H15, 3, 999) 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.

RE: adding cells with numbers & letters in

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.

RE: adding cells with numbers & letters in

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

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

RE: adding cells with numbers & letters in

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

Cheers,
Joerd

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

RE: adding cells with numbers & letters in

Doesnt custom format do this?

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

regs
IJR

RE: adding cells with numbers & letters in

IJR,

Actually, using a custom format is the other way around - and much easier for Excel. If you format a cell as General "USD", 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources