How to return a word from between nth comma and n+1 th comma?

Question

I want to extract a word from text which is separated by commas.

This is one of the data from users.

117573,117573,219,219,75,75,Messi,barcelona,53780217,forward,froquen@gmail.com

I want to get "Messi" from the data.

I can use the "text to columns" function but our data is dynamic and whenever I export it, I have to do that again and again.

I tried "MID, FIND" text formulas but in the next updates, the number of commas will change.

I am looking to a VBA code as:

Function_name(A2;6;7) => RETURN "Messi"

6= starting comma
7= the comma after the word

What version do you have, if later than 2013 and on a PC then you can use FILTERXML:https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml — Scott Craner, Oct 11 '21 at 19:36
You can use `Split(cellValue,",")` to get a zero-based array of values, from which you could pick the (eg) 6th one using `arr(5)` (first value is `arr(0)`) — Tim Williams, Oct 11 '21 at 19:40
Thank you so much, according to directed question find a solution what I am looking for. — Talha Okatan, Oct 11 '21 at 19:40
@TimWilliams Thank you so much, according to your tip, I found the correct search. Turning into an array is so brilliant :) — Talha Okatan, Oct 11 '21 at 20:06

score 0 · Answer 1 · edited Apr 17 '22 at 07:36

0

Found code from https://trumpexcel.com/vba-split-function/

Function ReturnNthElement(CellRef As Range, ElementNumber As Integer)
Dim Result() As String
Result = Split(CellRef, ",")
ReturnNthElement = Result(ElementNumber - 1)
End Function

edited Apr 17 '22 at 07:36

Community

1
1

answered Oct 11 '21 at 20:05

Talha Okatan

1
2

How to return a word from between nth comma and n+1 th comma?

1 Answers1