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

 

[hatstand]

If I understand you correctly, then this will do what you need: =IF(ISNUMBER(SEARCH("/bbb/",B1))=TRUE,A1&C1&B1,A1&C1)

The above assumes A1 is aaa, B1 has /bbb/ anywhere in the cell, C1 has ddd.com.

Hope it helps.

 

[spurtniq]

If I understand you correctly, then this will do what you need: =IF(ISNUMBER(SEARCH("/bbb/",B1))=TRUE,A1&C1&B1,A1&C1)

The above assumes A1 is aaa, B1 has /bbb/ anywhere in the cell, C1 has ddd.com.

Hope it helps.

You were SOOO close. My fault.

I edited the original comment to make more sense. Cell B2 is EMPTY and filled conditionally based on
the content of cell A2.

Edit Below:

in cell A2 is text

aaa

(length will vary)

OR

aaa/bbb/ccc

IF is found text

/bbb/

in cell A2 THEN

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.

 

[spurtniq]

Here's an example of what I'm currently using to test for the existence of "/bbb/"

=IF(ISNUMBER(SEARCH("/bbb/",A2)),"", A2&$D$2)

the "" in the above formula is what would need to change as noted
based on the existence of "/bbb/"

Hope that helps.

 

[hatstand]

So if cell A1 has the letter "a" as the length can vary or "/bbb/" then you want to something otherwise do something else.


This searches for /bbb/ or any number of letter "a"s.
=IF(OR(ISNUMBER(SEARCH("/bbb/",A1))=TRUE,ISNUMBER(SEARCH("a*",A1))=TRUE),1,2)

Am I on the right track?

 

[spurtniq]

So if cell A1 has the letter "a" as the length can vary or "/bbb/" then you want to something otherwise do something else.


This searches for /bbb/ or any number of letter "a"s.
=IF(OR(ISNUMBER(SEARCH("/bbb/",A1))=TRUE,ISNUMBER(SEARCH("a*",A1))=TRUE),1,2)

Am I on the right track?

Not sure, when I plug in my variables based on your formula, I get 1's regardless of whether or not
the text "/bbb/" is in cell A1. Not sure what's supposed to be happening with the second search:

"ISNUMBER(SEARCH("a*",A1))"

but, it looks as though it's searching for text beginning with "a" - "a*"

"aaa" is an example

the text does not necessarily begin with the letter a

better example:

the constant code (for example) is .linkedin.com

so, if the text is

mysite/tagged/tuesday

where mysite = aaa

the end result would be:

mysite.linkedin.com/tagged/tuesday

if on the other hand the text is

anothersite (aaa)

the end result would be:

anothersite.linkedin.com

If it were instead:

anothersite/tagged/yesterday

the end result would be:

anothersite.linkedin.com/tagged/yesterday

Hope that helps.

 

[hatstand]

Here are a few ways:
A1 = Where you enter the variable
B1 = The formula
C1 = Text "/tagged/Tuesday"
Dd = Text ".Linkedin.com"
E1 = The constant

This is saying if A1 equals E1 then say 1. If it does not equal the 2. Otherwise blank.
=IF(A1=E1,1,IF(A1<>D1,2,""))

replacing the 1 and 2 with the cell ref's will give you what you want. Use '&' to add the cells together. eg. =A1&B1

Any better?

 

[spurtniq]

I've thought of doing something comparable to what you're suggesting.

For that to work, I've got to have some way of parsing out, from variable
length text, everything to the right of "/tagged/" including "/tagged/" and
inserting it in the cell to the right of the original text while stripping it from
the original text so that it can all be properly concatenated. It would also
mean rewriting the formula because I could no longer search for "/tagged/"
in the original text as it would be parsed out.

Either there's a way to rearrange the existing text accordingly or some
method of parsing out the text so it can then be reconstructed accordingly.

Basically, all I want to do is insert between everything up to "/tagged/"
(along with everything to the right of that and everything to the left of that,
my constant based on the existence of the text "/tagged/".

If not, then I just want to concatenate the original text with my constant.

I can't just use the delimiter "/" as there are two of them in text where
the text "/tagged/" exists.

AHA!

I could search and replace "/tagged/" with (for example) "#/tagged/" and
then act on the delimiter "#".

That should make it easier to parse, maybe even easier to construct a formula
for, since I would then be looking for everything to the right (or left) of the "#".

Good idea?

 

[hatstand]

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)

 

[spurtniq]

Have to let you know later when I get home.

Thanx for the input though - MUCH appreciated.