Getting specific string before a character in VBA

Question

I wanted the leftmost string from a specific string.

Ex. sds_djfh_jdj I want sds for this I used the

LEFT('string',FIND("_",'string')-1)

but in my case another character may appear before the _ character

Ex. sds#_djfh_jdj (I want just sds but the formula that I use will give me sds#)

Can someone tell me how do I solve the problem.

If the vba code will use too much of time then I would prefer a formula otherwise vba code would also work. — Alex Sebastian, May 06 '20 at 14:48
If your strings have some pattern like the "_" you can use the VBA function `Split()` which will let you split your strings into an array, starting from position 0. So, `Split(Str, "_")(0)` would be `sds` — Damian, May 06 '20 at 14:51
If it's always the first 3 characters of the string you can use `Left(string, 3)` — Gitty, May 06 '20 at 14:54
That is the problem. The strings don't have any pattern because of which it becomes more difficult. — Alex Sebastian, May 06 '20 at 14:56
Have a look at [this](https://stackoverflow.com/a/22556970/97581940) on how to include some `OR` logic with `FIND`. With that logic use array entered: `=LEFT(A1,MIN(IFERROR(FIND({"#";"_"},A1),""))-1)` — JvdV, May 06 '20 at 14:57
**Unclear** do you want a sequence of letters to the left of either the underscore the pound symbol or to the left of any non-letter character ?? — Gary's Student, May 06 '20 at 15:06

JvdV · Accepted Answer · 2020-05-06T16:07:27.157

2

As commented, you can include some OR logic within FIND. For all your cases it appears that the following would do just fine:

=LEFT(A1,MIN(IFERROR(FIND({"#";"_"},A1)-1,LEN(A1))))

Note: It's an array formula and need to be confirmed through CtrlShiftEnter

edited May 06 '20 at 16:07

answered May 06 '20 at 15:36

JvdV

But there's one thing which causes a problem when there is just the string that I want that is sds then the formula gives an error – Alex Sebastian May 06 '20 at 15:47
@AlexSebastian, please see update. If this answered your question do not forget to accept the answer (click the checkmark to the left of the answer) – JvdV May 07 '20 at 09:04

1 Answers1