Thread: [Solved] Extracting data from a cell with multiple lines of text
View Single Post
 
Old 02-11-2015, 10:42 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,343
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
To do this properly, you should output each of Name, Address1, Address2, City, ST/Zip & Phone to separate columns. That way the data can be used for label/list generation via a Word mailmerge. Ideally, you'd do the text-to-columns processing for these data with a macro for all data fields. For the fields you're interested in, though, you can do it with formulae, thus:
Name =MID(A1,SEARCH("Name:",A1)+6,SEARCH("Credit Limit:",A1)-SEARCH("name:",A1)-6)
Address1 =MID(A1,SEARCH("Address1:",A1)+10,SEARCH("Balance: ",A1)-SEARCH("Address1:",A1)-10)
Address2 =MID(A1,SEARCH("Address2:",A1)+10,SEARCH("Aging Bucket:",A1)-SEARCH("Address2:",A1)-10)
City =MID(A1,SEARCH("City:",A1)+6,SEARCH("Cus Grp:",A1)-SEARCH("City:",A1)-6)
ST/Zip =MID(A1,SEARCH("ST/Zip:",A1)+8,SEARCH("Assigned Sales:",A1)-SEARCH("ST/Zip:",A1)-8)
Phone =MID(A1,SEARCH("Phone:",A1)+7,SEARCH("Tear#:",A1)-SEARCH("Phone:",A1)-7)
where the cell to be processed is A1.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote