-4

Im trying to extract number from a given format lets say ABC-DEFG-HIJ but i ran into trouble. Anyone knows how to extract the given format in excel into 3 small numbers (ABC, DEFG and HIJ) based on "-" ??

As pointed out by few ppl, i have included excel code for extracting ABC, DEFG . I have trouble extracting HIJ

=LEFT(B4;FIND("-"; B4)-1) returns ABC

=MID(B4;FIND("-";B4)+1;FIND("-";B4;FIND("-";B4)+1)-FIND("-";B4)-1) returns DEFG,
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Bobble Head
  • 1
  • 3
  • 2
    Multiple ways, where text-to-columns comes to mind as being your first go-to solution. Furthermore, have a look at string functions like `MID()`, `LEFT()` etc. Alternatively go with [`FILTERXML()`](https://stackoverflow.com/q/61837696/9758194) to "split" data based on a delimiter. If you have done your research, try to come up with a solution and post back the problems you are facing. Btw, if you do want to go VBA-route there is an actual [`SPLIT()`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function) function available... – JvdV Feb 22 '21 at 13:21
  • 2
    *"I ran into trouble"* that implies you have tried something. So please [edit] your question and show what you have tried. – Pᴇʜ Feb 22 '21 at 13:21

2 Answers2

1

Same idea as your formulas for left and middle part:

=LEFT(B4,FIND("-",B4)-1)
=MID(B4,FIND("-",B4)+1,FIND("-",B4,FIND("-",B4)+1)-FIND("-",B4)-1)
=RIGHT(B4,LEN(B4)-FIND("-",B4,FIND("-",B4)+1))
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
0

Try this formulas:

= TRIM( LEFT( SUBSTITUTE( B4, "-", REPT( " ", LEN(B4) ) ), LEN(B4) ) )
= TRIM( MID( SUBSTITUTE( B4, "-", REPT( " ", LEN(B4) ) ), LEN(B4), LEN(B4) ) )
= TRIM( RIGHT( SUBSTITUTE( B4, "-", REPT( " ", LEN(B4) ) ), LEN(B4) ) )
EEM
  • 6,601
  • 2
  • 18
  • 33