Help!

Parsing Data with a $ symbol

 
  

Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Data Map RSS
Next:  Can't complete Mail Merge in Word 2003 version  
Author Message
shiver16



Joined: Mar 13, 2009
Posts: 2



PostPosted: Fri Mar 13, 2009 1:01 pm    Post subject: Parsing Data with a $ symbol

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:

light 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.

Thanks again.
Back to top
shiver16



Joined: Mar 13, 2009
Posts: 2



PostPosted: Fri Mar 13, 2009 3:55 pm    Post subject:

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?
Back to top
bwoody123



Joined: Apr 25, 2007
Posts: 12



PostPosted: Fri Jul 17, 2009 12:13 pm    Post subject: parsing with $

=IF(ISERR(MID(E3,FIND("$",E3),5)),"0", MID(E3,FIND("$",E3),5))

should work.
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Data Map All times are: Eastern Time (US & Canada) (change)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum