VB Function - is this possible?
VB Function - is this possible?
Author
Discussion

Smiler.

Original Poster:

11,752 posts

261 months

Wednesday 22nd August 2018
quotequote all
Thinking about String Manipulation, is it possible to select all characters before a particular character?

So for the format:

XX.05.0001 - Description blah blah.nnn

Select everything up to the "-" on the left.

.nnn is the filetype.


I know certain things can be done, like selecting the first xNº characters, but this isn't suitable.

TIA.

gothatway

7,756 posts

201 months

Wednesday 22nd August 2018
quotequote all
Smiler. said:
Thinking about String Manipulation, is it possible to select all characters before a particular character?

So for the format:

XX.05.0001 - Description blah blah.nnn

Select everything up to the "-" on the left.

.nnn is the filetype.


I know certain things can be done, like selecting the first xNº characters, but this isn't suitable.

TIA.
Something like :
Left(String,InStr(,String,"-"))
I cannot remember whether they're 0- or 1-based, but playing with that should get you there.

Harpoon

2,554 posts

245 months

Wednesday 22nd August 2018
quotequote all
Instr function should do the job and feed the result from that into a Left function. Something like (not tested!):

Left(myString,Instr(myString,"-"))

gothatway

7,756 posts

201 months

Wednesday 22nd August 2018
quotequote all
Harpoon said:
Instr function should do the job and feed the result from that into a Left function. Something like (not tested!):

Left(myString,Instr(myString,"-"))
Snap !! But I think there is a starting position parameter in InStr, hence the leading "," in my example.

Harpoon

2,554 posts

245 months

Wednesday 22nd August 2018
quotequote all
Indeed. I thought start point was optional so the compiler doesn't moan about the initial "," but I could be wrong

bridge87

43 posts

183 months

Wednesday 22nd August 2018
quotequote all
You can also do this without using VBA to simplify it a bit.
=left(string,find(" - ")-1)

Edited by bridge87 on Wednesday 22 August 14:06