Pulling Info from Column

[SouthFloridaBadger]

Hi all,

I'm copying and pasting data from a PDF and need to eliminate some unnecessary data. The original .pdf has 4 columns: Account, Dollar Amount, Name, Notes

When I copy this to an excel document, it copies the row from all 4 columns into column A. The two columns I need are Account and Dollar Amount. All account numbers are 8 digits so I was able to create a formula to weed that out with this:
=left(A1,8)

The problem I'm running into is obtaining the dollar amount within the cell.
For example:

A1 contains: 11112222 $1234.56 Sample, Name Sample Note

I pull the 11112222 with =left(A1,8) in column B but not sure how to pull dollar amount to column C.

Further, the dollar amount varies from $1.01 to $10,000+

Let me know if you need any further clarification. I appreciate the help!

[Speshul]

Where A1 has the data:

Please Login or Register  to view this content.

[sktneer]

May be this.....

Please Login or Register  to view this content.

[AlKey]

to get dollar amount try this one

=TRIM(MID(SUBSTITUTE(MID(A1,FIND("$",A1),255)," ",REPT(" ",255)),1,255))

[SouthFloridaBadger]

Sure, here is a sample with varying dollar amounts

samplebook.xlsx

[Speshul]

=MID(A1,FIND("$",A1),FIND(" ",A1,10)-FIND("$",A1))

should work fine

or any of the above suggestions!

[SouthFloridaBadger]

All of the above suggestions worked, thank you for speedy reply!!

[AlKey]

Actually you can use just one formula to get the account and dollar amount

Copy and paste formula in B1 and pull it to C1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),(COLUMNS($A1:A1)-1)*255+1,255))

	A	B	C
1	11112222 $1234.56 Sample, Name Sample Note	11112222	$1234.56

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Speshul]

Actually you can use just one formula to get the account and dollar amount

Copy and paste formula in B1 and pull it to C1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),(COLUMNS($A1:A1)-1)*255+1,255))

	A	B	C
1	11112222 $1234.56 Sample, Name Sample Note	11112222	$1234.56

oh that's pretty cool, didn't know about that combination

I'd use that one if you need to split everything off, it works every time it finds a space too so it'll separate everything if you copy to F. If there is always a comma after the third criteria....you could triple the formula length to get rid of it!

Please Login or Register  to view this content.

lol