0

I know this questions seems redundant but cannot find an answer. I have a text string such as:

A-B-C-D-E-F

I would like to extract only the first 4 items separated by "-". Result should be

A-B-C-D

Alternatively, I am also looking to extract only the 3rd item:

C

I have been trying the MID and SEARCH functions with no success. Any help would be well appreciated.

AP38
  • 159
  • 7

4 Answers4

1

Using TEXTSPLIT (As of this writing only available to Office 365 Insider Beta Channel)

=TEXTJOIN("-",TRUE,INDEX(TEXTSPLIT(A1,"-"),1,{1,2,3,4}))

Change the {1,2,3,4} to 3 for the third.

If one does not have TEXTSPLIT but has TEXTJOIN and is on a PC then:

=TEXTJOIN("-",TRUE,INDEX(FILTERXML("<a><b>"&SUBSTITUTE(A1,"-","</b><b>")&"</b></a>","//b"),1,{1,2,3,4}))

Without TEXTJOIN it will require vba. Like this UDF that mimics TEXTJOIN: MS Excel - Concat with a delimiter

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Yup, still learning the new formula. – Scott Craner Apr 26 '22 at 21:16
  • Thank you so much @ScottCraner ! Unfortunately, I get an error message #NAME?. Perhaps this is coming from my version of Excel ? I am on Mac V16.60 office 365. – AP38 Apr 26 '22 at 22:13
  • I also don't seem to have TEXTSPLIT although I do have TEXTJOIN – AP38 Apr 26 '22 at 22:23
  • That is why neither work. FILTERXML is not available on MAC and TEXTSPLIT is currently in Beta and only available to Insiders. @AP38. Hopefully it will be released to all soon. – Scott Craner Apr 26 '22 at 22:42
1

So, this is what I have tried,

FORMULA_SOLUTION

• Formula used in cell B1

=TEXTJOIN("-",,TAKE(TEXTSPLIT(A1,"-"),,4))

• Formula used in cell D1

=TEXTJOIN("-",,DROP(TEXTSPLIT(A1,"-"),,-2))

• Formula used in cell C1

=TEXTJOIN("-",,INDEX(TEXTSPLIT(A1,"-"),,3))

Note: Formulas shown above works for O365 Users, Insiders Beta Channel users only!

However, if you have access to Excel 2019, then you can use either TEXTJOIN() or CONCAT()

enter image description here

• Formula used in cell B9

=SUBSTITUTE(CONCAT("-"&INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A9,"-","</s><s>")&"</s></t>","//s"),ROW(A1:A4))),"-","",1)

• Formula used in cell C9

=SUBSTITUTE(CONCAT("-"&INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A9,"-","</s><s>")&"</s></t>","//s"),3)),"-","",1)

Since OP has mentioned in comments, that OP is using O365 in MAC, hence here is an update.

FORMULA_SOLUTION

• Formula used in cell B1

=TEXTJOIN("-",,TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),COLUMN(A1:D1)*99-98,99)))

• Formula used in cell C1

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),COLUMN(C1)*99-98,99))

Or,

• Formula used in cell D1

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),200,100))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
0

For Excel MAC and earlier versions:

Extract first four:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),4))-1)

Extract the third:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))-1)

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

Easier to understand (now and in the future) if done in small steps.
Assuming source data is in A2:
in B2: =FIND("-",$A2)
in C2: =FIND("-",$A2,B2+1)
in D2, E2: copy across from C2
Then in F2: =LEFT(A2,E2-1)
You can put all of that into one formula, but that's more error-prone.

zsalya
  • 454
  • 4
  • 8