I have an excel document with a 42,000 entries (all in one column) and i need the name and postcode taken from each company and placed into columns.
PLease see attached file, the top records on sheet 2 is how i want the data and then if you scroll down to row 387 this is how the data is in its original formnikolas 1.xlsx
How do you know the first and last lines of a record? Your sample is inconsistent and your "after" data did not come from the "before" sample.
Last edited by protonLeah; 07-31-2013 at 03:52 PM. Reason: spelling
Ben Van Johnson
[QUOTE=protonLeah;3345087]How do you know the first and last lines of a record? Your sample is inconsistent and your "after" data did not come from the "berore" sample.[/Q
usually is every 5 lines the start and finish of every record
Using your posted workbook and making some "educated guesses"...
This regular formula begins the list of row numbers that contain company names:
Formula:
Please Login or Register to view this content.
And these two regular formula returns the company name and postal code for each company
Formula:Please Login or Register to view this content.
Copy C1:E1 down as far as you need.
With your sample data, these are the results:
Note: The 4th company name is incorrect because there is no absolutely consistent pattern in your data.
Please Login or Register to view this content.
I'm hoping, though, that most of the calculated values will be correct.
Is that something you can work with?
Is not working. Excel does not accept the formula. Shows problem here ("Tel:",$A$10
Last edited by nik7; 08-01-2013 at 03:31 AM.
Do your region/language settings require semicolons instead of commas in formulas?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks