Need a excel formula

[aryanaveen]

Hi All,

Can someone please help me with a formula for below situation.
in below situation I need a formula in column B which will extract from right till first "-" from right, formula in column C should extract from right till second "-"

	A	B	C	D
1	ABC Com - INC - 123 - XYZ	XYZ	123 - XYZ	INC - 123 - XYZ
2
3

 

[hrayani]

Hi,

If the text length is same in column C & D then you can use below formulas

In Cell B1

=RIGHT(C1,3)

In Cell C1

=RIGHT(D1,9)

 

[aryanaveen]

Hi,

If the text length is same in column C & D then you can use below formulas

In Cell B1

=RIGHT(C1,3)

In Cell C1

=RIGHT(D1,9)

Text length wont be same

 

[hrayani]

Ok, Try this in B1 and let me know

=RIGHT(C1,LEN(C1)-SEARCH("-",C1))

 

[aryanaveen]

Ok, Try this in B1 and let me know

=RIGHT(C1,LEN(C1)-SEARCH("-",C1))

Formula should extract data from A1, hence modified the formula to =RIGHT(A1,LEN(A1)-SEARCH("-",A1)) and it results in INC - 123 - XYZ, I think this is searching from left but I need the formula which wil look from right

 

[hrayani]

Is A1 a text value or result of a formula ??

Edit

And will there always be 3 dashes (-) ??? in cell A1

 

[hrayani]

In B1

=RIGHT(A1,LEN(A1)-SEARCH("#",SUBSTITUTE(A1,"-","#",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

This will work no matter how many dashes in cell A1 & no matter what is the text length

Test it and let me know

 

[Peter_SSs]

You could try this.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 03 17.xlsm
	A	B	C	D
1
2	ABC Com - INC - 123 - XYZ	XYZ	123 - XYZ	INC - 123 - XYZ
3	XYZ Testing - ABCDE - 2435 - X	X	2435 - X	ABCDE - 2435 - X
Extract sections

Cell Formulas
Range	Formula
B2:D3	B2	=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE($A2,"-",REPT(" ",100)),COLUMNS($B:B)*100),REPT(" ",101)," -"))

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ABC Com - INC - 123 - XYZ	XYZ	123 - XYZ	INC - 123 - XYZ
2	ABCde Com - xyzINC - 456 - 1xyi23 - 123XYZ	123XYZ	1xyi23 - 123XYZ	456 - 1xyi23 - 123XYZ	xyzINC - 456 - 1xyi23 - 123XYZ
3	1 - 2 - a - b - cde - xyz - 12345 - abc	abc	12345 - abc	xyz - 12345 - abc	cde - xyz - 12345 - abc	b - cde - xyz - 12345 - abc	a - b - cde - xyz - 12345 - abc	2 - a - b - cde - xyz - 12345 - abc
Sheet843

Cell Formulas
Range	Formula
B1:K3	B1	=IF(COLUMNS($B1:B1)<=LEN($A1)-LEN(SUBSTITUTE($A1,"-","")),SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE($A1,"-",REPT(" ",100)),COLUMNS($B1:B1)*100))," "," - "),"")

 

[jtakw]

Hi again,

Shorter version of my formula above (Post #9):

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	ABC Com - INC - 123 - XYZ	XYZ	123 - XYZ	INC - 123 - XYZ
2	ABCde Com - xyzINC - 456 - 1xyi23 - 123XYZ	123XYZ	1xyi23 - 123XYZ	456 - 1xyi23 - 123XYZ	xyzINC - 456 - 1xyi23 - 123XYZ
3	1 - 2 - a - b - cde - xyz - 12345 - abc	abc	12345 - abc	xyz - 12345 - abc	cde - xyz - 12345 - abc	b - cde - xyz - 12345 - abc	a - b - cde - xyz - 12345 - abc	2 - a - b - cde - xyz - 12345 - abc
Sheet843

Cell Formulas
Range	Formula
B1:I3	B1	=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE($A1,"-","-"&REPT(" ",100)),COLUMNS($B4:B4)*100)),$A1,"")