CODEWITHSUNDEEP
excelexcel-formula
Rosellx
Rosellx

Reputation: 147

How to extract specific text from a field in Excel?

I have the following sample of data where I want to extract a specific text from the Order description field.

For example: There are 3 records of sales with the same Order ID and Invoice ID, however, the Order Amount does not reflect the true total for each of these sales records.

Is there a way for me to extract the cost of items out from Order Description column in Excel? Please do share the formulas if there is any!

enter image description here

Thanks!

Upvotes: 1

Views: 560

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

If all your data follows same structure (that means the price starts with $ and it ends with , ) you can do it like this:

=MID(A1;SEARCH("$";A1);SEARCH(", ";A1)-SEARCH("$";A1))

enter image description here

EDIT: Wrap everything inside a VALUE function, so the output will be a number.

Upvotes: 2

JvdV
JvdV

Reputation: 75860

Well, you could use FILTERXML:

enter image description here

Formula in B2:

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,",","")," ","</s><s>")&"</s></t>","//s[starts-with(., '$')]"),1)

If you don't have Excel O365, you could strip off the INDEX(). I have it in there to take the first amount from the resulting array of amounts to prevent spilling it.

Upvotes: 2

Related Questions