conditional find text - rearrange text (formula)

[spurtniq]

in cell A2 is text

aaa

(length will vary)

OR

aaa/bbb/ccc

IF is found text

/bbb/

in cell B2

I want a formula that will conditionally concatenate:

aaa.ddd.com/bbb/ccc

otherwise text will be:

aaa.ddd.com

where ".ddd.com" (minus quotes) is a constant stored elsewhere
(say cell "D2") for this example.

Make sense?

Thanx

 

[spurtniq]

You could utilise this into a formula. it will return the text to the left of the first /. If no / is found it returns the cells value. Getting closer?

=IF(FIND("/",CONCATENATE(A2,"/"))<=LEN(A2),MID(A2, 1, FIND("/",A2)-1),A2)

Tried this. It gave me the text to the right.

1) Easiest method I've come up with is to add a delimiter "#" using most any text editor
with search and replace
2) Copy / Paste the edited text into a spreadsheet
3) Convert Text to Columns at the delimiter "#"
4) Then apply the concatenation and constants

This formula worked for me:

=IF(ISNUMBER(SEARCH("/tagged/",B2)),A2&$F$4&B2, A2&$F$4)

with the constant

.linkedin.com ($F$4)

and

mysite

in A2 (no "/tagged/")

becomes

mysite.linkedin.com

anothersite/tagged/today

or converted, delimited (#), text to columns:

anothersite#/tagged/today

becomes

anothersite (A2)

/tagged/today (B2)

and by my formula

anothersite.linkedin.com/tagged/today

tain't pretty, but, it works.

Once I've got it setup as a template, in just a few minutes I can convert
several links quickly, easily.

Hope it proves useful / helpful.

Thanx

 

[Peter_SSs]

Does this formula (copied down) do what you want?

Or do you only want the D2 value included if there is a particular text after the / (eg tagged)?

If the formula below does not do what you want, please just list the exact values you would want returned in B2:B6 for my sample data in column A.

Excel Workbook
	A	B	C	D
1
2	aaa	aaa.ddd.com		.ddd.com
3	aaa/bbb/ccc	aaa.ddd.com/bbb/ccc
4	anothersite/tagged/yesterday	anothersite.ddd.com/tagged/yesterday
5	xyz	xyz.ddd.com
6	mysite/tagged/Tuesday	mysite.ddd.com/tagged/Tuesday
7
Insert Text