How to extract

[ahovander1213]

Need a formula or macro to extract specific info

Below in bold is what I am pasting into Excel as 1 column, 4 rows
I don't care about row 1

I can use LEFT function to get the 1st 6 digits.

My issue is how to I get middle (example 2,405.66 in row 2). My issue is there is no consistency.
/1 has various blank cells before it gets to amount i want. After amount I also get various blank cells.

In my example I have underlined the data I want to extract.


PO #/Line Rel # Amount Paid Qty Received
505979/1 2,405.662.00
505979/2 1,271.68 1.00
506339/1 2,405.66 2.00

 

[shg]

Data > Text to columns, tick Delimited, Next, tick Space, Finish.

 

[Fluff]

If you will always have a space either side of the 2nd number you want, how about

Excel 2013/2016
	A	B	C
1	505979/1 2,405.66 2.00	505979	2405.66
2	505979/2 1,271.68 1.00	505979	1271.68
3	506339/1 2,405.66 2.00	506339	2405.66
Sheet3

Cell Formulas
Range	Formula
B1	=LEFT(A1,SEARCH("/",A1)-1)*1
C1	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))*1

 

[ahovander1213]

B formula is working perfectly
C formula is giving me a result of #VALUE !

 

[ahovander1213]

Thanks for the feedback. Never thought about DATA

 

[Fluff]

Will the 2nd number that you want always have a space both before & after it?

 

[jtakw]

Hi,

Based on your samples in OP, assuming the 2nd number (Amount Paid) Always has 2 decimal places after the period.
Use B2 formula for PO# if there may be PO#s with leading 0 (zeros), result remains as Text, Use C2 formula if there Won't be leading zeros, results converted to Real Numbers (drops All leading zeros).
D2 formula for Amount Paid, results converted to Real Numbers.

Book1
	A	B	C	D
1	PO #/Line Rel # Amount Paid Qty Received	PO#	PO#	Amount Paid
2	505979/1 2,405.662.00	505979	505979	2405.66
3	505979/2 1,271.68 1.00	505979	505979	1271.68
4	506339/1 2,405.66 2.00	506339	506339	2405.66
5	012345/2 245.053.00	012345	12345	245.05
Sheet326

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("/",A2)-1)
C2	=LEFT(A2,FIND("/",A2)-1)+0
D2	=MID(A2,FIND(" ",A2),FIND(".",A2)-FIND(" ",A2)+3)+0

 

[ahovander1213]

yes

 

[jtakw]

yes

??

Is that a response to Fluff's question in Post #6 ??

 

[Rick Rothstein]

If you will always have a space either side of the 2nd number you want,

The number of spaces vary (click "Reply With Quote" to see the OP's actual layout).