# Parsing data using \$ symbol

#### shiver16

##### New Member
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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi and welcome to the board!!
Code:
``IF(ISNUMBER(FIND("\$",E3),MID(E3,FIND("\$",E3),5),0)``

lenze

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:

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

Sorry, I omitted a )
Code:
``=IF(ISNUMBER(FIND("\$",A1)),MID(A1,FIND("\$",A1),5),0)``
or
Code:
``=IF(ISERROR(FIND("\$",A1)),0,MID(A1,FIND("\$",A1),5))``

lenze

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?

What works great??

Mid will return a text value. Add 0 to convert to a number
Rich (BB code):
``=IF(ISNUMBER(FIND("\$",A1)),MID(A1,FIND("\$",A1),5)+0,0)``
lenze

"=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.

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

Replies
5
Views
221
Replies
0
Views
507
Replies
0
Views
574
Replies
10
Views
491
Replies
2
Views
176

Threads
1,206,811
Messages
6,075,002
Members
446,113
Latest member
FadDak

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back