LEFT Function with a specific Character 1

[Topcat04]

Hi All
I am looking for the correct formula to return the data to the LEFT() from a specific character in this instance a :

Im guessing its a combination of either FIND or LEN or both with the LEFT() but haven't been able to get it to work.
using one of the previous posts, I tried using FIND with a negative value, but that returned a VALUE.

two instances where the len differs is below
1263245-01:METAL CONTROLLER
CPFA105660-01RO EX MINI OPP

Appreciate your support,

 

[SkipVought]

Hi,

What formulas have you tried?

 

[dhookom]

You seem to have the key words. Consider searching the web.

Sorry about this as I thought the posting was in the SQL Server Programming forum. My bad...

Duane
Hook'D on Access
MS Access MVP

 

[jonsi]

assuming you want the : to be the character you're looking for then

=LEFT($E10,((FIND(":",$E10))-1)) will work if your data is in Cell E10 ...this formula assumes that you don't want the ) colon hence the -1

Why would you need to do this?
If all you are hoping to do is separate say a Stock code from a Stock description, then Text-to-Columns would be the way to go

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[MakeItSo]

How about using Split?
Split(yourstring, ":")(0)



"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[Topcat04]

Jonsi
thanks for both solutions, both worked really well.
The Text to columns is something that was so simple, but completely overlooked! Thanks for the post and advice,
cheers!