Extracting Dollar Amounts from Cell

[Spyderturbo007]

I'm trying to help a client with an exported CSV file from a website. Unfortunately, there is no way for me to change how the data is exported. You pretty much get what you get.

One cell contains a ton of data, but I only need the dollar amounts from that cell. Some cells don't have any dollar amounts and other cells can have 2 or more dollar amounts at different positions within the cell. I'm also dealing with HTML tags in the cell like &nbsp and <br>.

I've tried Text to Columns and it kind of works, but since the dollar amounts can appear anywhere in the cell I get tons of columns and the dollar amounts don't align so I can't delete entire columns of the data I don't need.

Essentially, I need to remove the dollar amounts from the cell and put each one in its own column and then delete everything else in the cell. So, column I would have the combined data and $X.XX from that cell would go in column J, $Y.YY would go in K and so on.

For example, here is the data in some of cells. I'm not sure how this will look once this post is posted, but I'll give it a shot. Each section is actually all the data in a single cell. Thanks so much for the help!

Port & Company Essential Fleece Pullover Hooded Sweatshirt<br><b>Size:</b>&nbsp;Adult XL<br><b>Color:</b>&nbsp;Jet Black<br><b>Personalization</b>&nbsp;Name:&nbsp;Johnson - $5.00<br><b>Personalization</b>&nbsp;Number:&nbsp;2 - $2.50 Port & Company Fan Favorite Tee<br><b>Size:</b>&nbsp;Adult XL<br><b>Color:</b>&nbsp;Athletic Royal<br><b>Personalization</b>&nbsp;Name:&nbsp;Johnson - $5.00<br><b>Personalization</b>&nbsp;Number:&nbsp;2 - $2.50 Holloway Ladies Retro Grade Jacket<br><b>Size:</b>&nbsp;Adult Medium<br><b>Color:</b>&nbsp;Black / White<br><b>Personalization</b>&nbsp;Name:&nbsp;Joshnson - $5.00

 

[queuesmef]

Is Power Query an option to investigate?

 

[Marc L]

​

As this is very not a csv format ! But it can be easily added to a html document in order to be pasted within a worksheet …​

 

[Spyderturbo007]

I'm not familiar with a power query, but I'm willing to try anything.

It's exported by as a CSV file from the hosting companies website. It's what they give you when you go to Export -> CSV. Opening with Notepad++ shows the separators. It's just that one cell that is all jammed together. Everything else gets it's own column. They have been struggling with the export since the beginning.

Thanks to everyone for their help!

 

[Marc L]

As any csv file does not contain html code …​

 

[Spyderturbo007]

I appreciate the response and am by no means an Excel or CSV expert. All I know is that when I open the Administrative console associated with the webstore and hit Export -> CSV, that's the file I get. Either way, I would still really appreciate help.

 

[RoryA]

Based on those examples, something like this:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,"<br>","-"),"&","&#38;"),"-","</y><y>")&"</y></x>","//y[contains(., '$')]"))

which should spill across as needed in 365.

 

[Spyderturbo007]

@RoryA that's incredible! it appears to work perfectly for the instances that contain dollar amounts. Is there any way to have it error handle for cells that don't contain dollar amounts? I get #VALUE! for those cells, which isn't a big deal but figured I'd ask.

Can you explain how that formula actually works, or point me in the right direction so I can understand what is happening?

Thank you so much. That formula will honestly save someone about 4 hours of work every month. You have no idea how much that will help them in the long run.

 

[RoryA]

Just wrap it in IFERROR(formula,"")

The SUBSTITUTE functions do a bit of clean up (XML shouldn't have literal & symbols in it) and then replace <br> with - before then replacing all hyphens with a closing and opening y tag. By prepending the "<x><y>" and appending the closing "</y></x>" we get a valid XML structure where x is basically the entire cell and each y element contains the sections of the cell broken up by hyphens. (I chose hyphens as they appear just before the $ amounts in each case.) So, for example, your first example cell gets converted to this text:

The second argument to the FILTERXML function is an XPath structure that tells it to return any y elements that contain a $ symbol. The Transpose is there because otherwise the output array would spill vertically.

Does that make sense?