multiple - on excel but need certain data

[sksanjeev786]

Hi Team,

I need only brand data i,e coke,pepsi,sprit .etc.
can you please suggest any formula to get only brads

Q- Brand imagey- Midwest- Coke
Q- Brand imagey- West- Pepsi
Q- Brand imagey- Ontrio- Sprite
Q- Brand imagey- Omha- Fanta
Q- Brand imagey- Midwest- Limca
Q Brand imagey Ontrio- Fruti

Thanks.
Regards,
Sanjeev

 

[arthurbr]

Select the column
Data - Text to column - Delimited - Next - Enter hyphen as delimiter (check "Other) - Next -Select each column you don't need and check "Do not import" - Finish
or

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99),3),100))

and pull down

 

[Peter_SSs]

or

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99),3),100))

and pull down

I would remove the '3' (instance number) in case there are more or less than 3 "-" characters, as in the final example of the sample data, even though that may have been a typo?

20 10 16.xlsm
	A	B
1	Q- Brand imagey- Midwest- Coke	Coke
2	Q- Brand imagey- West- Pepsi	Pepsi
3	Q- Brand imagey- Ontrio- Sprite	Sprite
4	Q- Brand imagey- Omha- Fanta	Fanta
5	Q- Brand imagey- Midwest- Limca	Limca
6	Q Brand imagey Ontrio- Fruti	Fruti
Extract

Cell Formulas
Range	Formula
B1:B6	B1	=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

 

[sksanjeev786]

I would remove the '3' (instance number) in case there are more or less than 3 "-" characters, as in the final example of the sample data, even though that may have been a typo?

20 10 16.xlsm
	A	B
1	Q- Brand imagey- Midwest- Coke	Coke
2	Q- Brand imagey- West- Pepsi	Pepsi
3	Q- Brand imagey- Ontrio- Sprite	Sprite
4	Q- Brand imagey- Omha- Fanta	Fanta
5	Q- Brand imagey- Midwest- Limca	Limca
6	Q Brand imagey Ontrio- Fruti	Fruti
Extract

Cell Formulas
Range	Formula
B1:B6	B1	=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

Perfect

Thank you so much for your help. sir

 

[sksanjeev786]

Select the column
Data - Text to column - Delimited - Next - Enter hyphen as delimiter (check "Other) - Next -Select each column you don't need and check "Do not import" - Finish
or

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99),3),100))

and pull down

Perfect

Thank you so much for your help. sir

 

[sksanjeev786]

Hi Team,

Thank you all for the above clarification

Just wanted to check again it is possible to get only midwest, West, Omha etc.

 

[Peter_SSs]

is possible to get only midwest, West, Omha etc.

Is your sample in post 1 how your data can be? That is, can the data actually be like that last line of your sample where there is only one "-" character, or was that just a mistake with your sample and in fact every row has 3 "-" characters placed as shown in the first 5 rows of your sample.

If the data is uniform like the first 5 then you can still use Text to Columns in a similar way to that described in post 2, or by formula try ..

20 10 16.xlsm
	A	B
1	Q- Brand imagey- Midwest- Coke	Midwest
2	Q- Brand imagey- West- Pepsi	West
3	Q- Brand imagey- Ontrio- Sprite	Ontrio
4	Q- Brand imagey- Omha- Fanta	Omha
5	Q- Brand imagey- Midwest- Limca	Midwest
Extract (2)

Cell Formulas
Range	Formula
B1:B5	B1	=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),200,99))

 

[sksanjeev786]

Is your sample in post 1 how your data can be? That is, can the data actually be like that last line of your sample where there is only one "-" character, or was that just a mistake with your sample and in fact every row has 3 "-" characters placed as shown in the first 5 rows of your sample.

If the data is uniform like the first 5 then you can still use Text to Columns in a similar way to that described in post 2, or by formula try ..

20 10 16.xlsm
	A	B
1	Q- Brand imagey- Midwest- Coke	Midwest
2	Q- Brand imagey- West- Pepsi	West
3	Q- Brand imagey- Ontrio- Sprite	Ontrio
4	Q- Brand imagey- Omha- Fanta	Omha
5	Q- Brand imagey- Midwest- Limca	Midwest
Extract (2)

Cell Formulas
Range	Formula
B1:B5	B1	=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),200,99))

Awesome!!!

Thank you so much sir.

Regards,
Sanjeev

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.