Manipulating strings in two columns

Hi I have column A which has the following format "--string1"... and column b which has the format "string2".. what I need is a function that will replace the "string2" in column b with "--string2" to match the number of dashes in column A. This varies from 1 to 3 dashes and I want to add those same # of dashes to column B (without destroying the existing data)

Any help would be appreciated

Hi there,

Say the data starts in cell A1, enter the following formula =REPT("-",SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))/LEN("-"))&B1 into cell C1, copy it down as many rows as required, convert the formulas (or Column C) to values and delete Column B.

Kind regards,

Robert

HTH

You won't be able to do a direct replacement without some code. Along the same lines as Robert I'd use this formula in C1 copied down

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

If you want this in column B just copy column C, Edit > Paste Special > Values, to convert the formulas to values, then delete column B