How to extract

ahovander1213

New Member
Joined
Oct 13, 2018
Messages
10
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Data > Text to columns, tick Delimited, Next, tick Space, Finish.
 
Upvote 0
If you will always have a space either side of the 2nd number you want, how about


Excel 2013/2016
ABC
1505979/1 2,405.66 2.005059792405.66
2505979/2 1,271.68 1.005059791271.68
3506339/1 2,405.66 2.005063392405.66
Sheet3
Cell Formulas
RangeFormula
B1=LEFT(A1,SEARCH("/",A1)-1)*1
C1=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))*1
 
Upvote 0
Will the 2nd number that you want always have a space both before & after it?
 
Upvote 0
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
ABCD
1PO #/Line Rel # Amount Paid Qty ReceivedPO#PO#Amount Paid
2505979/1 2,405.662.005059795059792405.66
3505979/2 1,271.68 1.005059795059791271.68
4506339/1 2,405.66 2.005063395063392405.66
5012345/2 245.053.0001234512345245.05
Sheet326
Cell Formulas
RangeFormula
B2=LEFT(A2,FIND("/",A2)-1)
C2=LEFT(A2,FIND("/",A2)-1)+0
D2=MID(A2,FIND(" ",A2),FIND(".",A2)-FIND(" ",A2)+3)+0
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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

Which adblocker are you using?

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
Back
Top