Parsing data using $ symbol

[shiver16]

Hello,

I hope someone could point me in the right direction for this data I need to sort. In general it looks like this: All in one column

flight 1.1
flight 1.2 paid $50 owes $100
flight 1.5 paid $80
pass paid $125
ground 1.2 flight .9
checkout flight 1.2 paid $76

I need to extract the numbers with a $ sign into a separate column. to look like this:

flight 1.1
flight 1.2 paid $50 owes $100...........50..........100
flight 1.5 paid $80...........................80
pass paid $125...............................125
ground 1.2 flight .9
checkout flight 1.2 paid $76...............76

.............. representing separate columns

If anyone knows of an easy way to do this please let me know. I tried the text to columns feature using the $ but it only worked for 1 or 2 of the cells. Since all the data I want is preceded by a $ symbol I hope this can be done.

I figured out how to get it to display the value using:

=MID(E3,FIND("$",E3),5)

However, if there is no $ like many of my entries then it spits out #VALUE! How do I get it to just leave a 0 if there is no $ in the column?

 

[lenze]

Hi and welcome to the board!!

IF(ISNUMBER(FIND("$",E3),MID(E3,FIND("$",E3),5),0)

lenze

 

[shiver16]

Hi,

Thanks for the quick reply. This board is full of great info!

Unfortunatly when I try the formula it give me an error.

Here is what it does and the data Im trying to parse:

 

[Scott Huish]

If there is more than one amount in the cell is it always the last part or is it always preceded by owes?

 

[lenze]

Sorry, I omitted a )

=IF(ISNUMBER(FIND("$",A1)),MID(A1,FIND("$",A1),5),0)

or

=IF(ISERROR(FIND("$",A1)),0,MID(A1,FIND("$",A1),5))

lenze

 

[Milo_Minderbinder]

See this thread for a few ideas on parsing numbers from text

http://www.mrexcel.com/forum/showthread.php?t=374200

 

[shiver16]

Works great thanks! Now if I could just get the numbers to sum. The result keeps showing 0. Is it because of the $? Or number formatting?

 

[lenze]

What works great??

Mid will return a text value. Add 0 to convert to a number

=IF(ISNUMBER(FIND("$",A1)),MID(A1,FIND("$",A1),5)+0,0)

lenze

 

[shiver16]

"=IF(ISNUMBER(FIND("$",A1)),MID(A1,FIND("$",A1),5),0)"

this one does exactly what I need. Ill add the zero and see if it sums right.

Thanks again.

 

[Scott Huish]

Since you don't have to pull out multiple values, I think this will work too:

=(0&MID(E3,FIND("$",E3&"$")+1,LEN(E3)))+0