Excluding text before character

[shedboyxx]

Hi folks,

I have a feeling this is simple but I’m coming up blank. I have a field that contains text separated by backslashes. The number of backslashes are always the same

example 1
info1\info2\information3\info4\info5\here's what I want to exclude

I’m looking for a calculation that gives me all of the text that happens before the 5^th occurrence of a backslash.

example 2
info1\info2\information3\info4\info5

I tried using Find, Right and Left in different combinations but failed

Let me know if you have something that will work.

TIA

Jim

 

[T. Valko]

Hi folks,

I have a feeling this is simple but I’m coming up blank. I have a field that contains text separated by backslashes. The number of backslashes are always the same

example 1
info1\info2\information3\info4\info5\here's what I want to exclude

I’m looking for a calculation that gives me all of the text that happens before the 5<SUP>th</SUP> occurrence of a backslash.

example 2
info1\info2\information3\info4\info5

I tried using Find, Right and Left in different combinations but failed

Let me know if you have something that will work.

TIA

Jim

Try this...

=LEFT(A2,FIND("^",SUBSTITUTE(A2,"\","^",5))-1)

 

[Domenic]

Try...

=LEFT(A2,FIND("^^",SUBSTITUTE(A2,"\","^^",5))-1)

 

[T. Valko]

Try...

=LEFT(A2,FIND("^^",SUBSTITUTE(A2,"\","^^",5))-1)

Nice one!

 

[Domenic]

Try this...

=LEFT(A2,FIND("^",SUBSTITUTE(A2,"\","^",5))-1)

Yeah, I like yours too!

 

[shedboyxx]

Thanks for both versions!

Unfortunately I must be doing something wrong as I'm getting the dreaded #VALUE result. I copied and pasted as is and used the example data both the same reference cell (A2) and another cell (A9), changing the formula on the latter.

What could I be doing wrong? The cells are formatted as General

TIA

 

[T. Valko]

Thanks for both versions!

Unfortunately I must be doing something wrong as I'm getting the dreaded #VALUE result. I copied and pasted as is and used the example data both the same reference cell (A2) and another cell (A9), changing the formula on the latter.

What could I be doing wrong? The cells are formatted as General

TIA

If there aren't at least 5 of the slash characters you'll get a #VALUE! error. But you did say:

The number of backslashes are always the same

Can you post an example of the cell entry and the EXACT formula you tried that gives the error?

 

[shedboyxx]

Hello everyone let me reintroduce myself...I'm the Town Dope...
Ugh....

Target text was in B2 and I confused it with A2

It's working perfectly now. Thanks for your patience.

I did have one followup question since I've never used a caret (^) before in Excel. What does this character function as? If it's a very contextual answer then maybe just as it relates to this situation.

You guys are great!

Jim

 

[T. Valko]

Hello everyone let me reintroduce myself...I'm the Town Dope...
Ugh....

Target text was in B2 and I confused it with A2

It's working perfectly now. Thanks for your patience.

I did have one followup question since I've never used a caret (^) before in Excel. What does this character function as? If it's a very contextual answer then maybe just as it relates to this situation.

You guys are great!

Jim

The ^ symbol is typically used in math to mean "to the power of".

For example:

10^3 means 10 to the 3rd power or 10 * 10 * 10 = 1000

We can express that in a formula:

=10^3

 

[Cindy Ellis]

In the context of the find & substitute formula, is "^" just being used as a character that's very unlikely to be somewhere else in the string?