formula/vba help

lirkus84

New Member
Joined
Mar 18, 2016
Messages
8
hi friends,
I have the following txt strings that are not delimited and I would like to have the data separated

0120190624INIT 99999
0312345678920190621AB 000000000000000000 000000000000000000122365ABC
0310111213120190621AB 000000000000000000 0000000000000000001143ABC
0500002


the logic is as follows:

in the string:

first 2 digits 01 is the header row. if 05 it is the footer row.
if the first two digits are 03 denote that this is the row to be analyzed.
the next 9 digits after the 03 (i.e 123456789) denote an identifier that needs to be separated
the next 8 digits denote the date (20190621) this also needs to be separated

in the end of the string is the value I need after the last zero and having the numbers and letters separated (122365ABC)

the end result I want is below:

123456789 06/21/2019 122365 ABC

not sure what is the easiest way to accomplish this, whether formulas or vba.
All the lengths of characters that need to be extracted from the string are fixed as well as the format.

please help. TIA
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
in the end of the string is the value I need after the last zero and having the numbers and letters separated (122365ABC)
Is it of the fixed width format, so these values you need at the end of the string ALWAYS start in the same place (the 60th character space)?
Do you want the original data overwritten, or do you want what needs to be extracted put on a separate sheet?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
If my assumption is correct, and assuming that your data begins in cell A1 and goes down column A, you could get the values you want listed out in columns B, C, and D via simple worksheet formulas like this:
in cell B1: =IF(LEFT($A1,2)="03",MID($A1,3,9),"")
in cell C1: =IF(LEFT($A1,2)="03",MID($A1,12,8),"")
in cell D1: =IF(LEFT($A1,2)="03",MID($A1,60,LEN($A1)),"")
and then copy down for all rows.

If you want some sort of VBA solution, please answer the question regarding where you want these results to go.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,938
Members
416,143
Latest member
JoyceMB

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
Top