Extract data within 1 cell

[faizee]

Hi all
I have this data in one cell "L/L Helvatica font 2mg Vit (2ddd)"


1. i just want to exact "Helvatica" , first 3+1 letter space is fixed. i mean before the helvatica 4 letter space will be always there.


2. i want the data within first two spaces "Helvatica"

Please help

 

[Momentman]

Excel 2010
	A	B
1	L/L Helvatica font 2mg Vit (2ddd)"	Helvatica
Sheet7

Cell Formulas
Range	Formula
B1	=MID(A1,5,FIND(" ",A1,5)-5)

 

[Rick Rothstein]

I have this data in one cell "L/L Helvatica font 2mg Vit (2ddd)"

1. i just want to exact "Helvatica" , first 3+1 letter space is fixed. i mean before the helvatica 4 letter space will be always there.

2. i want the data within first two spaces "Helvatica"

Font names can consist of more than one word ("Times New Roman" for example), so if you use a formula that does exactly what you asked for, you will not always get the correct font name from it. Does the word "font" always follow the font name? If so, you could use this formula...

=TRIM(MID(LEFT(A1,SEARCH("font",A1)-1),5,99))

 

[faizee]

Font names can consist of more than one word ("Times New Roman" for example), so if you use a formula that does exactly what you asked for, you will not always get the correct font name from it. Does the word "font" always follow the font name? If so, you could use this formula...=TRIM(MID(LEFT(A1,SEARCH("font",A1)-1),5,99))

Thank you for both of.youBut I could not.describe my query clearly.My data in one could be any, likeMy name is faizeehis class is fiveSo I want to exact the word between first two space, likeMy name is faizee -----> "name"His class is five ------> "class"

 

[njimack]

Sheet2

*	A	B
1	L/L Helvatica font 2mg Vit (2ddd)	Helvatica
2	My name is faizee	name
3	his class is five	class

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:213px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =MID(SUBSTITUTE(SUBSTITUTE(A1," ","#",1)," ","^",1),FIND("#",SUBSTITUTE(SUBSTITUTE(A1," ","#",1)," ","^",1))+1,FIND("^",SUBSTITUTE(SUBSTITUTE(A1," ","#",1)," ","^",1))-FIND("#",SUBSTITUTE(SUBSTITUTE(A1," ","#",1)," ","^",1))-1) B2 =MID(SUBSTITUTE(SUBSTITUTE(A2," ","#",1)," ","^",1),FIND("#",SUBSTITUTE(SUBSTITUTE(A2," ","#",1)," ","^",1))+1,FIND("^",SUBSTITUTE(SUBSTITUTE(A2," ","#",1)," ","^",1))-FIND("#",SUBSTITUTE(SUBSTITUTE(A2," ","#",1)," ","^",1))-1) B3 =MID(SUBSTITUTE(SUBSTITUTE(A3," ","#",1)," ","^",1),FIND("#",SUBSTITUTE(SUBSTITUTE(A3," ","#",1)," ","^",1))+1,FIND("^",SUBSTITUTE(SUBSTITUTE(A3," ","#",1)," ","^",1))-FIND("#",SUBSTITUTE(SUBSTITUTE(A3," ","#",1)," ","^",1))-1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Rick Rothstein]

Thank you for both of.youBut I could not.describe my query clearly.My data in one could be any, likeMy name is faizeehis class is fiveSo I want to exact the word between first two space, likeMy name is faizee -----> "name"His class is five ------> "class"

This formula will do that...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",500)),501,500))

 

[faizee]

Thank you both of you
Rick Rothstein formula worked 100%
but njimack also worked. but gives error when there are only two values in a cell

Rick, can you please also tell me the formula, if i need the values. after first space and before third space,
means two words,
for L/L Helvatica font 2mg Vit (2ddd),, the result should be "Helvatica font"
and for My name is faizee, the result should be "name is"

 

[XOR LX]

Hi,

Simply change the final 500 to 1000 in the formula provided.

Regards