In need of a formula

[Carrguy]

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Ron Coderre]

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Carrguy]

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Ron Coderre]

How about this...

In Step_3 of the Text-to-Columns, set the destination to $B$1 (instead of
$A$1).
That will leave the original data intact while putting the parsed data in
the cells to the right of the originals.

OR
If you REALLY WANT a formulaic approach, here's one:

B1: =LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1,CHAR(10),CHAR(7),1))-1)

C1:
=SUBSTITUTE(LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1,CHAR(10),CHAR(7),2))-1),LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1,CHAR(10),CHAR(7),1))),"")

D1:
=SUBSTITUTE(LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1,CHAR(10),CHAR(7),3))-1),LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1,CHAR(10),CHAR(7),2))),"")

Copy those down as far as you need.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Ron Coderre]

I forgot to include the last formula...

E1:
=SUBSTITUTE($A1,LEFT($A1,FIND(CHAR(7),SUBSTITUTE($A1,CHAR(10),CHAR(7),3))),"")

***********
Regards,
Ron

XL2002, WinXP

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Biff]

Ok, you've been "warned" !!!! <VBG>

Formula for cell B1:

=LEFT(A1,FIND(CHAR(10),A1)-1)

Formula for C1:

=LEFT(SUBSTITUTE(A1,B1&CHAR(10),""),FIND(CHAR(10),SUBSTITUTE(A1,B1&CHAR(10),""))-1)

Formula for D1:

=LEFT(SUBSTITUTE(A1,B1&CHAR(10)&C1&CHAR(10),""),FIND(CHAR(10),SUBSTITUTE(A1,B1&CHAR(10)&C1&CHAR(10),""))-1)

Formula for E1:

=MID(SUBSTITUTE(A1,B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10),""),1,255)

Note: no error checking. Assumes all entries will have the same format of 4
lines.

See, I told you it would be easier to use Text to Columns first, then
reassemble the original entry!!!

Biff

I tried that. I wouldlike to keep all information in cell A1 as well. As
well
as create a formula that takes the desired information and places it in
the
B1-E1 cells. Any other suggestions would be greatly apprecieated

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1.
Second
line to be in C1. I want to extract contents from A1 to the respective
cells
to the right. I need a formula in which I can put into place as I have
100+
lines to do and want to take the formula and carry it down the spread
sheet.
Information changes on each line such as detail, feature and my
tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have
made it
into four original cells but, I can't convince work to do it. So this
is what
I have to work with. It is a locked forum. I am sure I need a formula
in each
cell B1,E1 to do this but unable to find one. I am not sure if I can
post my
email but will do so. If you can give me a formula you can email it to
me
that would be great, (e-mail address removed)

 

[Carrguy]

Thanks Ron and Biff you guy s are great you saved me a lot of time here. The
data text is a great formula. When i typed my message out i was trying to
make it easy and i should not have been like that. I need to break up the
lines not from B1-E1. Rather there is columes in between thus the formula
works great as i can move them to other cells. One last question i was trying
to follow the formula and learn it!!! I have one question in the formula line
i am lookikng for. I cant explain i will show it

orginal
detail
feature
1.025
± .010
If i dont have a detail in every line how do i re-adjust the formula to do
so.

Thanks agian guys you are truley amazing to make this work for me

I forgot to include the last formula...

E1:
=SUBSTITUTE($A1,LEFT($A1,FIND(CHAR(7),SUBSTITUTE($A1,CHAR(10),CHAR(7),3))),"")

***********
Regards,
Ron

XL2002, WinXP

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Carrguy]

Also can i transfer to other work sheets.

I forgot to include the last formula...

E1:
=SUBSTITUTE($A1,LEFT($A1,FIND(CHAR(7),SUBSTITUTE($A1,CHAR(10),CHAR(7),3))),"")

***********
Regards,
Ron

XL2002, WinXP

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Ron Coderre]

First...Biff's formulas are more compact.

Second, what are the rules for determining which items are skipped?
Is the Detail item the only possibility?
Can more than one item be skipped?
Are there text indicators that could be used as flags to determine the item
type of each line? (eg the Feature line always contains the word "feature",
etc)

***********
Regards,
Ron

XL2002, WinXP

Also can i transfer to other work sheets.

I forgot to include the last formula...

E1:
=SUBSTITUTE($A1,LEFT($A1,FIND(CHAR(7),SUBSTITUTE($A1,CHAR(10),CHAR(7),3))),"")

***********
Regards,
Ron

XL2002, WinXP

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated


:

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)

 

[Carrguy]

Cell A1 can have no more then 4 lines. The norm will be three lines in cell
a1 ifirst line "feature",2nd line if i can make it a number for the flag like
my size .877 or 1.235 i can do that, there are to many words eg. dia, dim,
runout, etc. 3rd line "tolerance". If there is no line four i get a error on
my spread sheet in cell e1 as there is nothing there to post.

next ? Instead of puting the formula in line b1 i want to make it sheet2 b1.
do i jsut need to put the name of the sheet in the front of the formula eg.


='sheet1'!SUBSTITUTE($A1,LEFT($A1,FIND(CHAR(7),SUBSTITUTE($A1,CHAR(10),CHAR(7),3))),"")


the rule for skipping a item is if there is no drawing detail to refrence
too i dont put it the ord "Detail" in.

First...Biff's formulas are more compact.

Second, what are the rules for determining which items are skipped?
Is the Detail item the only possibility?
Can more than one item be skipped?
Are there text indicators that could be used as flags to determine the item
type of each line? (eg the Feature line always contains the word "feature",
etc)

***********
Regards,
Ron

XL2002, WinXP

Also can i transfer to other work sheets.

I forgot to include the last formula...

E1:
=SUBSTITUTE($A1,LEFT($A1,FIND(CHAR(7),SUBSTITUTE($A1,CHAR(10),CHAR(7),3))),"")

***********
Regards,
Ron

XL2002, WinXP


:

I tried that. I wouldlike to keep all information in cell A1 as well. As well
as create a formula that takes the desired information and places it in the
B1-E1 cells. Any other suggestions would be greatly apprecieated


:

I think this might be the easiest way....

Select your column of data.

From the Excel main menu:
<data><text-to-columns>
Select: Delimited.....Click [Next]
Check: Other
Click in the character box next to other
Hold down the [Alt] key...type 0010....release the [Alt] key

(That sets line break as the delimiter)
Click the [Finish] button

That should parse the Col_A items across to the right at each linebreak

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

A1 B1 C1 D1 E1
Detail "B"
Feature #21
1.0265 dia
±.010 Detail "B" Feature #21 1.0265 Dia ±.010

Basically I want to take the first line and want to place it in B1. Second
line to be in C1. I want to extract contents from A1 to the respective cells
to the right. I need a formula in which I can put into place as I have 100+
lines to do and want to take the formula and carry it down the spread sheet.
Information changes on each line such as detail, feature and my tolerances.
All lines in cell A1 are ALT Entered to go to next line I would have made it
into four original cells but, I can’t convince work to do it. So this is what
I have to work with. It is a locked forum. I am sure I need a formula in each
cell B1,E1 to do this but unable to find one. I am not sure if I can post my
email but will do so. If you can give me a formula you can email it to me
that would be great, (e-mail address removed)