organizing data in excel when there is no header nor delimiter

i have an excel file, that has data listed in a funny way. there is no header column or row

the data is only separated by an identifier number in the first column
and the rest of the data i need is in different columns and rows

here is an example the = is a different column and each line is a different row

m12345 = =smith, john a = = = = = = = = =
= =DOB: = 12/12/1918= = = = male = = = =
= = home address: = = = = = = = = =
= = 1234 = = = =anystreet dr = = = = =
= = san antonio = = = = = TX = = = = 12345

m2345 = = lopes, joe = = = = = = = = =
= = DOB =1/1/1 = = = = male = = = =



how do i extract the last name first name dob, address, city, state, zip, gender for each person

any help would be helpful i need to organize the data correctly so i can use mail merge but since there are no headers and it's not organized it doesnt work

thanks

SAMPLE WORKBOOK.xlsx





basically i need to sort the data from how i get it into how i want it but there are over 1000 records

any help would be appreciated i was thinking of doing a macro or maybe there is a formula i can use

or i heard something about parsing but i am not familiar i have been trying to figure this out for three days

and i need to have it done and know how to do it by tomorrow my job depends on it

thank you

OMG thank you so much

that did it......

your awesome

thanks, well it worked on the file i had open

but then i closed it and tried to use the formula on a second file and i am getting a lot of
ref errors circular reference on the first formula

that response was for SDCH

glen i see the summary and the raw data

but i'm a novice here i have no idea what array data is

what i would like to do

say i get a list of 2000 people some are duplicates

individuals are say 1200 or so but that may change


could be less or more people

I want to be able to have a formula ready that i can just paste into the sheet and it will organize it for me

that's pretty much what you did i believe but the array and columns a and b threw me off

is there a way to do like 2500 lines and just sort by name i would uncheck blank and #value that way if there are less names i won't get a bunch of grabage at the bottom of the list