Dear all, How can do that? A1= ABCDEFG B1=CD...

(Etwo Cargo) #1

Dear all, How can do that?

A1= ABCDEFG B1=CD Which formula i need to use to have in C1 ABEFG?

if B1=AB, C1 must change automatically to CDEFG.

(Suvrutt Gurjar) #2

Hi,

Please try the expression given below in the Column C

CONCATENATE(LEFT([ColumnA],(FIND([ColumnB],[ColumnA])) - 1 ),RIGHT([ColumnA],( 6

  • (FIND([ColumnB],[ColumnA])))))

Hope this helps.

The above expression assumes that you have a character string of 7 characters in Column A and Column B always has 2 continuous characters as youhave given in sample ,such as AB,BC,CD etc.

(Aleksi Alkio) #3

How about SUBSTITUTE(SUBSTITUTE([A1],LEFT([B1],1),""),RIGHT([B1],1),"")