Extract 1st, 2nd, 3rd, 4th, etc value of a cell

[Ananthak275]

I have a cell that have values combined together split by "-". Need to extract the 1st, 2nd, 3rd, 4th, etc value

Eg:

Name - Address - Details - ID - Plan	B	C	D	E	F
AK - Ontario - Phone - 123123 - 50	AK	Ontario	Phone	123123	50
GFD- Ontario - Phone - 754523- 234	GFD	Ontario	Phone	754523	234
SDB- Quebec- Phone - 241- 79	SDB	Quebec	Phone	241	79
SDA- Ontario - Phone - 5123- 124	SDA	Ontario	Phone	5123	124

 

[jtakw]

Hi,

Try:

Book3.xlsx
	A	B	C	D	E	F
1	Name - Address - Details - ID - Plan
2	AK - Ontario - Phone - 123123 - 50	AK	Ontario	Phone	123123	50
3	GFD- Ontario - Phone - 754523- 234	GFD	Ontario	Phone	754523	234
4	SDB- Quebec- Phone - 241- 79	SDB	Quebec	Phone	241	79
5	SDA- Ontario - Phone - 5123- 124	SDA	Ontario	Phone	5123	124
Sheet1048

Cell Formulas
Range	Formula
B2:F5	B2	=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))

 

[Ananthak275]

Hi,

Try:

Book3.xlsx
	A	B	C	D	E	F
1	Name - Address - Details - ID - Plan
2	AK - Ontario - Phone - 123123 - 50	AK	Ontario	Phone	123123	50
3	GFD- Ontario - Phone - 754523- 234	GFD	Ontario	Phone	754523	234
4	SDB- Quebec- Phone - 241- 79	SDB	Quebec	Phone	241	79
5	SDA- Ontario - Phone - 5123- 124	SDA	Ontario	Phone	5123	124
Sheet1048

Cell Formulas
Range	Formula
B2:F5	B2	=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))

Is there a way i can do individual values per column? eg: Column B get the 2nd value between "-". Column C, get the 3rd Value

 

[jtakw]

That's what it does.

B2 formula is copied down and across to F5

Results are Exactly as you wanted in your OP.
Unless, now you're saying to Skip the First value?

 

[Ananthak275]

That's what it does.

B2 formula is copied down and across to F5

Results are Exactly as you wanted in your OP.
Unless, now you're saying to Skip the First value?

Hi Sorry this is on me. A more accurate example would be seen below, where I have some columns where the value in Column A:

eg : Corleone Tres Matthew - Ontario Land Line Development - Phone Plans - No Variation - Change User to Another Land Line

Can you adjust your code to get the values with this as an example ^ ? thank you

 

[jtakw]

This modified version should do, B2 formula copied down and across as needed:

Book3.xlsx
	A	B	C	D	E	F
1	Name - Address - Details - ID - Plan
2	AK - Ontario - Phone - 123123 - 50	AK	Ontario	Phone	123123	50
3	GFD- Ontario - Phone - 754523- 234	GFD	Ontario	Phone	754523	234
4	SDB- Quebec- Phone - 241- 79	SDB	Quebec	Phone	241	79
5	SDA- Ontario - Phone - 5123- 124	SDA	Ontario	Phone	5123	124
6	Corleone Tres Matthew - Ontario Land Line Development - Phone Plans - No Variation - Change User to Another Land Line	Corleone Tres Matthew	Ontario Land Line Development	Phone Plans	No Variation	Change User to Another Land Line
Sheet1048

Cell Formulas
Range	Formula
B2:F6	B2	=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",LEN($A2))),COLUMNS($B2:B2)*LEN($A2)-LEN($A2)+1,LEN($A2)))

 

[AhoyNC]

Another option is to use the "text to columns" under the Data ribbon.
Choose Delimited option.