• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract add numbers from alphanumeric strings in single cell w/ multiple lines

C

carlosgcastroi

New Member
Hello everyone,

I'd love to hear your ideas on how to deal with this excel challenge I am facing. I have alphanumeric strings (Column B). The numbers outside parenthesis are quantities I'd like to add/sum up. The information in parenthesis states the type of units. Column A shows the delivery number. Some cells (e.g Refer to B3, B4 and B8 below) contain multiple lines in one single cell. I'd like to add the numbers in each cell. For example, Cell B3 should be 70+95+85 or Cell B4 570+45. What I'd like to have is a single number (Quantity) for each Delivery number. I also put these example in google doc located at

https://docs.google.com/spreadsheet/ccc?key=0AlC-547k9QQpdDFMZ2V2TGEyLU5Dbmd2ZEYwTDY1b1E

Thanks,

Carlos


Column A Column B

Row Delivery Qty

1 85596221 5780(M5)

2 85596146 800 (A1)

3 85596145 70 (A2)

95 (A5)

85 (A1)

4 85596790 570 (C5)

45 (C2)

5 85599363 4000(Bulk5)

6 85599219 15912(Bulk20)

7 85599266 14251(Bulk1)

8 85599221 2074(B2)

7827(B5)
 
@ Fred

thank you for the formula. But it only works for the single-line cells. It does not work on the multiple-line cells (i.e. B3, B4 and B8). I do appreciate your prompt reply.
 
Hi, carlosgcastroi!

Check this: http://www.2shared.com/document/eaQ4NQMP/2011-12-15_Excel_Question__for.html

It works for up to three value per celd, I guess if you have more you can handle with the modifications.

Regards!
 
Hi, carlosgcastroi!

Re-uploaded: http://www.2shared.com/document/QcZMYmsm/2011-12-15_Excel_Question__for.html

Made a mistake in 2nd and 3rd substrings.

Please note that you've got an inconsistent value, there is a cell that displays Value/Error, cause it lacks the opening parenthesis.

Regards!
 
@ SirJB7 - Thank you for the reply. I didn't reply earlier but the formula worked well. I was aware of the Value/Error and it comes with the data entry so that's part of scrubbing the data. Thanks again.
 
hi,

can any one help me to fine a date in excel with related multiple data


For Eg:

MRKU2215275 9060370391

MRKU2215275 9060578644

MRKU2215275 9060580450

MRKU2215275 9063176095

MRKU3367164 9063320918

MRKU3367164 9064117570

MRKU3367164 9061607008

MSKU0686160 9062314657


if i put MRKU2215275 all the data related to that cell have to list down like this

9060370391

9060578644

9060580450
 
@sathis85

Hi!

You should start a new topic as stated here in third green sticky post:

http://chandoo.org/forums/

Regards!


EDIT: I see you already did it.
 
You must log in or register to reply here.
Back
Top