extract data from a row with a lot of data

[Pamella]

Hello,

I have a file which I have to extract data from. Unfortunately when it is converted to Excel it all prints to Column A but separate rows.

First condition within the file search in column A, within row - 1) Find "Host Fee"
2) Find "Sum Total"

Move 1 row down.

The no. of rows may vary could be between 1-4
CUSTOMER US DOLLAR XXXX .00 .0 56.99 88.9 55.6 44.5 10.00
CUSTOMER US DOLLAR YYYY .00 .0 56.99 88.9 55.6 44.5 20.00
CUSTOMER AUD DOLLAR XXXX .00 .0 56.99 88.9 55.6 44.5 30.00
CUSTOMER AUD DOLLAR YYYY .00 .0 56.99 88.9 55.6 44.5 40.00

loop until the word "DDDD" is reached

I need it to extract in different columns and paste to Outputsheet
1) Currency,
2) Bits which are XXXX AND YYYY
3) the last amounts i.e. 10.00, 20.00 , 0.00 and 40.00

output should look like

colA colB colC
Curr TYPE AMOUNT
US XXXX 10.00
US YYYY 20.00
AUD XXXX 30.00
AUD YYYY 40.00

[p45cal]

when it is converted to Excel

How are you doing this? There's probably another way of getting the information into Excel correctly.
Perhaps attach such an (unaltered) file here?

[Pamella]

The data is coming from a pdf. Unfortunately, I cannot attach the file due to its sensitive contents.
I used vba to convert from pdf to Word then Word to Html then html into Excel. I tried directly from Acrobat pro to excel the output is not nice.

[Artik]

Unfortunately, I cannot attach the file due to its sensitive contents.

I think you can attach an Excel workbook with data. Falsify sensitive data.

Artik

[Pamella]

I’m afraid the file is too big. I have tried to put a snippet of the sample data that needs to be extracted. In the rows where the data is held there are different number of spaces seperating each field.
I can't seem to update the original post but column A row 189 for example would look like CUSTOMER US DOLLAR XXXX .00 .00 56.99 88.9 55.6 44.5 10.00. (I tried to add varying spaces between the values but this site is not allowing.)

[Artik]

Although you're trying, we still do not know what the data layout in the sheet looks like.

Artik

[Pamella]

thanks all for trying. Can I close this thread?