CODEWITHSUNDEEP
excelvba
RobbTe
RobbTe

Reputation: 405

Excel: get value between characters, but if empty copy cell?

I am using the formula below to get a value between the the first and second "|". However sometimes there is no value. The formula will result in #VALUE! So, is it possible to get the value between the first and second "|" character, but if there is no value, copy the whole cell?

The formula i use is: =TRIM(LEFT(SUBSTITUTE(MID(B2;FIND("|";SUBSTITUTE(B2;"|";"|";2))+1;LEN(B2));"|";REPT(" ";LEN(B2)));LEN(B2)))

The result i would like is:

|---------------------|------------------|
|      Collected      |       Data       |
|---------------------|------------------|
|       Value 2       |  Value|Value 2   |
|---------------------|------------------|
|       Value         |      Value       |
|---------------------|------------------|

I guess this needs a module? Thanks a lot.

Upvotes: 1

Views: 31

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Try using IFERROR()?

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(G2;FIND("|";SUBSTITUTE(G2;"|";"|";2))+1;LEN(G2));"|";REPT(" ";LEN(G2)));LEN(G2))),TRIM(G2))

Upvotes: 1

Related Questions