Extract Data Using Macro

Question

Extract Data Using Macro

Anonymous

I would like to extract numbers (underlined) from the string of text below and map it to columns on another sheet.

The mapping is as follows:

"AB123" to column "Number"

"9XWBW" to column "Registration"

"61" to "Adult"

"2" to "Child"

"0" to "Infant"

"3488" to "Cargo"

"0" to "Mail"
"438" to "Baggage"

The format from the string of text below is always the same. How can I set-up a macro to extract the required data accordingly?

LDM
AB123/16.9XWBW.J10Y100.2/6
-XYZ.61/2/0.0.T9138.1/2401.2/4296.4/2441.PAX/1/62.PAD/0/0
.LTF/44L.PIL/25R.RMD/26/0
SI BW 120975
BI 65.2
SERVICE WEIGHT ADJUSTMENT WEIGHT/INDEX
ADD
NIL
DEDUCTIONS
NIL
XYZ FRE 3488 POS 0 BAG 438
END OF DOCUMENT

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

3 answers

Answer 1

Concatenate all lines and use a blank as delimiter.

Replace each delimiter (/ and .) with a blank.

Replace double blanks with one blank till no double blanks are found.

Split the data by blank, now your words are always in the same position ... if your input is really always the same.

Andreas.

Answer 2

Anonymous

How is this done using macro?

Answer 3

Depends on how you get the text into Excel ... Power Query / Copy&Paste ... one cell / multiple cells.

Create a sample file.

IMPORTANT: Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

BTW, at the moment I'm limited on time, an answer may take a while, please be patient.

Andreas.

Share via

Extract Data Using Macro

3 answers