Text to Columns maybe?

[griffo]

Hi there

I have a file that we output each day where I need to split the detail of a column based on some variables.

In the sample, you can see the different types of outputs that are available. For each output, there can be up to 3 "Lines:" depending on user input. The only mandatory field is "Field One:". The 3 "Lines" can have a maximum of 18 characters only (but anything under that is ok), if that is of any use. The examples shown are the format the database outputs depending on how many lines of info the user inputs (what I mean is the only variables are the bits between the "Line" and the <br>.

What I want to be able to do is split the data in the cell to extract the info from Field One, Line 1, Line 2 and Line 3 if they exist. Ideally what I'd like to do is insert 4 columns to the right of the existing data, put in a "header row" of Field One, Line 1, Line 2, Line 3 and then have the data populate under those headings, if that is possible.

Unfortunately I cannot change the output from the database, so that is not an option.

Have I got any hope of achieving this?

Cheers
Griffo

Field One:NIPb r
Field One:KODAb rLine 1:12334567890b r
Field One:Tacob rLine 1:123 Test Streetb rLine 2:123 456 7890b r
Field One:TEDb rLine 1:Ph: 1234 567 890b rLine 2:18 Testing Streetb rLine 3:City Details Hereb r

EDIT - changed to b r as didn't realise it was going to insert breaks! b r exports as br with <> around the br

 

[Cindy Ellis]

A simple text to columns won't do the trick, but with a helper column and a few manual steps (which could be automated) you can do what you've described. Note: The steps described below aren't elegant, but they work.
Another note: If your input data can include asterisks, choose a character other than "*" in the formula, and use that character in the text to columns dialog, described below.

First, with your data in column 1, enter the following formula in cell B1 (copy paste for now, but save it in a separated template for future use):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"<br>","*"),"Field One:",""),"Line 1:",""),"Line 2:",""),"Line 3:","")

Copy this down to all of your rows.

Now, highlight all of column B, and copy - paste values. You will lose your formulas, but you need text for "text to columns" to work.

With column B still selected, choose Text to Columns, choose "delimited", and enter an asterisk "*" in the "other" text box. Click Finish, and your output should be what you want.

If this works with your real data, and if you'd like to automate it with a macro, just reply back.

Hope this helps,
Cindy

 

[griffo]

Hi Cindy, thanks for your help so far!

What you have suggested does part of what I need, but I didn't quite get there.

Your suggestion has stripped out the "Field One" and "Line 1 Line 2" etc but left the br<> behind.

When I did the Data/Text to Columns, nothing happened (I did copy/paste special the values first).

Now that I'm back on my own computer, I've attached some rows so you can see what I start with and what I've ended up with following your steps. Any additional help will be greatly appreciated.

Cheers
Griffo

EDIT - the "screenshot" below is different to what i see in Excel. I don't see new lines, I see all the text in each cell is on one line with<> br (where the br sits in between the<> ).

orderitems.xls
	A	B	C	D
1	FieldOne:KODALine1:0404000123	KODA0404000123
2	FieldOne:TacoLine1:66TestStLine2:0422111222	Taco66TestSt0422111222
3	FieldOne:TEDLine1:Ph:0410999888Line2:18TestingStreetLine3:TestCity	TEDPh:041099988818TestingStreetTestCity
orderitems

 

[chrisl]

Not sure if this is what you need.
The formulae aren't pretty because I used some helper columns and then consolidated back into one formulae.

Book1
	A	B	C	D	E
5	Original	FieldOne	Line1	Line2	Line3
6	FieldOne:NIP	NIP
7	FieldOne:KODALine1:12334567890	KODA	12334567890
8	FieldOne:TacoLine1:123TestStreetLine2:1234567890	Taco	123TestStreet	1234567890
9	FieldOne:TEDLine1:Ph:1234567890Line2:18TestingStreetLine3:CityDetailsHere	TED	Ph:1234567890	18TestingStreet	CityDetailsHere
Sheet1

 

[chrisl]

Seems like formulae didn't come through in paste.
They are

Cell B6: =MID(A6,FIND(":",A6)+1,FIND("<",A6)-FIND(":",A6)-1)

Cell C6: =IF(ISNUMBER(FIND("Line 1",$A6)),MID($A6,FIND("Line 1:",$A6)+7,FIND("<",$A6,FIND("Line 1:",$A6)+6)-(FIND("Line 1:",$A6)+6)-1),"")

Cell D6: =IF(ISNUMBER(FIND("Line 2",$A6)),MID($A6,FIND("Line 2:",$A6)+7,FIND("<",$A6,FIND("Line 2:",$A6)+6)-(FIND("Line 2:",$A6)+6)-1),"")

Cell E6: =IF(ISNUMBER(FIND("Line 3",$A6)),MID($A6,FIND("Line 3:",$A6)+7,FIND("<",$A6,FIND("Line 3:",$A6)+6)-(FIND("Line 3:",$A6)+6)-1),"")

Then copy B6:E:6 down for four rows

 

[griffo]

Hi chrisl

That is genius - have just done a test and that worked perfectly!

Now to decipher how that actually works......

Thanks very much, greatly appreciated!

Cheers
griffo

 

[chrisl]

No problems Griffo

Each formula is basically a MID of the cell in column A
First one starts when it finds a colon and stops when it finds a <
Next one starts 7 characters after where it finds "Line 1" and stops when it find the next <
and so on for line 2 and 3

For the 2nd, 3rd, 4th formula, there is a test to make sure that the respective line 1,2 or 3 is actually in that particular cell.

 

[Cindy Ellis]

Sorry for the late reply to your reply...I think I'm on the other side of the world There was a substitute for the "<br>" in the formula...but it disappeared there as it did in your original post.
I know you have a solution...but here's the formula as intended...but you'll have to remove the extra set of quotes from around the "<" and ">" that surround the br:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,""<"br">"","*"),"Field One:",""),"Line 1:",""),"Line 2:",""),"Line 3:","")

(It's been more challenging to get the formulas to display on this one than to solve the original problem!)

Cindy

 

[xenou]

Okay, just spent 10 minutes trying to figure out how these formulas were finding ">" in the cells...Forgot the OP mentioned these marks! Cindy, your solution jogged my memory there. Nice ideas here.