Help with formulas: Left Right Mid Substitute etc...

[Desu Nota from Columbus]

I need to learn and understand how to do the following:

Take something like this: 123/Banana

to: 123 or: Banana


How do you search for a specific character, and then say return all characters to the left or right?

Examples:

1) aaaa-bbbbb-cc-ddddddd ---> aaaabbbbbccdddddd

2) A-1234/REDRUM ----> A1234REDRUM

3) 12/23/2006 - 12:54:33 -----> either 12/23/2006 or 12:54:33


Thanks,

Josh

 

[VoG]

See if this helps

Excel Workbook
	A	B	C
1	A-1234/REDRUM	A1234REDRUM
2	123/Banana	123	REDRUM
Sheet1

 

[Ron Coderre]

There are several approaches you might use.
Here are some:

With A1: 123/Banana
these formulas return: 123 and Banana
B1: =LEFT(A1,FIND("/",A1)-1)
C1: =SUBSTITUTE(A1,B1&"/","")

With A1: aaaa-bbbbb-cc-ddddddd
this formula returns: aaaabbbbbccddddddd
B1: =SUBSTITUTE(A1,"-","")

With A1: A-1234/REDRUM
this formula returns: A1234REDRUM
B1: =SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/","")

With A1: 12/23/2006 - 12:54:33 PM
these formulas return: 12/23/2006 and 12:54:33 PM
B1: =--TRIM(LEFT(A1,FIND("-",A1)-1))
C1: =TRIM(RIGHT(A1,LEN(A1)-FIND("-",A1)))

Does that help?

 

[jasonb75]

Just a few examples of many possibilities, in each case A1 is used as the cell holding the data

1) =substitute(A1,"-","")

2) =substitute(substitute(A1,"-",""),"/","")

3) Has multiple answers depending on the format of the data
For date =int(A1) or =trim(left(A1,find("-",A1)))
For time =mod(A1,1) or = trim(mid(A1,find("-",A1),len(A1))) or =trim(substitute(A1,left(A1,find("-",A1)),""))

 

[VoG]

Oops! Formula in C2

Excel Workbook
	A	B	C
1	A-1234/REDRUM	A1234REDRUM
2	123/Banana	123	Banana
Sheet1

 

[Desu Nota from Columbus]

Thanks for the responses everyone! I understand what I am seeing, I will definitely do a couple practice runs to cement in my mind.

I have a couple questions to help understand why this works:

LEN(A2)-FIND("/",A2)

Does the Find() function return a position of a character essentially making the this phrase return a number (pertaining to a the amount of characters to return)?


=LEFT(A2,FIND("/",A2)-1)
Assuming the answer (from above) is 'yes the find function returns the position of the character', why do you need -1? And what does the -1 mean?

Could you do something like this? =LEFT(A2, ABS((LEN(A2)-LEN(A2)-FIND("/",A2))+1)

 

[VoG]

FIND returns the character position reading from left to right. If you want what is to the left of that character you need to subtract 1

 

[jasonb75]

Does the Find() function return a position of a character essentially making the this phrase return a number (pertaining to a the amount of characters to return)?

Yes, it does.

=LEFT(A2,FIND("/",A2)-1)
Assuming the answer (from above) is 'yes the find function returns the position of the character', why do you need -1? And what does the -1 mean?

Using the find fuction alone would return the position of "/", so in 123/Banana it would return 4, if you wanted the result to be what is before "/" then that is only 3 characters, or (4) -1

Could you do something like this? =LEFT(A2, ABS((LEN(A2)-LEN(A2)-FIND("/",A2))

You could but it would be pointless, cutting it apart, ABS((LEN(A2)-LEN(A2)-FIND("/",A2) would return exactly the same as FIND("/",A2)

LEN()-LEN() = 0 so all you're doing is ABS(-FIND()) which is the same as FIND()

 

[Desu Nota from Columbus]

Thank you all for the help.