Extracting text from cell into different columns without using "Text to Columns" function

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,155

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jun 26, 2014

• #2

If the length of each string is same which in this case is 8, then something like this will work, you can drag the formula in B1 across the cells

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	SLVT920H SLVT921H SLVT923H SLVT935H SLVT978A SLVT910H SLVT911H SLVT979H SLVT982H	SLVT920H	SLVT921H	SLVT923H	SLVT935H	SLVT978A	SLVT910H	SLVT911H	SLVT979H	SLVT982H
Sheet5

Cell Formulas
Range	Formula
B1	=MID($A$1,9*(COLUMN(A$1)-1)+1,8)

If the lengths are not fixed, the formula becomes more complex,

WHY DON'T YOU WANT TO USE TEXT TO COLUMNS?

 

Upvote 0

D

dermie_72

Well-known Member

Joined

Sep 4, 2012

Messages

1,666

• Jun 26, 2014

• #3

you could use the mid function.
=MID(A1,1,8), then in the next column =MID(A1,10,8) and the enxt column =MID(A1,19,8)

You could put the Lengths, and start values in the header.

If you don't want to use text to column, then that is an option.

 

Upvote 0

S

striker359

Board Regular

Joined

Jun 19, 2014

Messages

64

• Jun 26, 2014

• #4

@Momentman: Coz i want to take this opportunity to learn about doing it functionally and it kind of helps to automate the process as well.. so all I need to do is have the information and the text are extracted automatically.

@dermie_72: That works when they are of equal length.. any idea what I should do if they happen to be of different character lengths?

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,155

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jun 26, 2014

• #5

I have uploaded some sample data and the result the formula gives

The formula in B1 can be dragged across the columnsto give you the results and also dragged down the rows for as many entries as you have

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	SLVT920H SLVT921H SLVT923H SLVT935H SLVT978A SLVT910H SLVT911H SLVT979H SLVT982H	SLVT920H	SLVT921H	SLVT923H	SLVT935H	SLVT978A	SLVT910H	SLVT911H	SLVT979H	SLVT982H
2	The boy is a great player	The	boy	is	a	great	player
3	Ask and it shall be given unto you	Ask	and	it	shall	be	given	unto	you
4	MrExcel is a wonderful forum	MrExcel	is	a	wonderful	forum
Sheet5

Cell Formulas
Range	Formula
B1	=IFERROR(MID(" "&$A1&" ",FIND("*|",SUBSTITUTE(" "&$A1&" "," ","*|",COLUMN(A$1)))+1,FIND(" "," "&$A1&" ",FIND("*|",SUBSTITUTE(" "&$A1&" "," ","*|",COLUMN(A$1)))+1)-FIND("*|",SUBSTITUTE(" "&$A1&" "," ","*|",COLUMN(A$1)))-1),"")

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jun 26, 2014

• #6

You can also do it with the sligthly shorter:

=IFERROR(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",113)),113*(COLUMNS($A:A)-1)+1,113)),"")

Again, copy to the right as required.

Regards

 

Upvote 0

S

striker359

Board Regular

Joined

Jun 19, 2014

Messages

64

• Jun 26, 2014

• #7

@Momentman: Your code gave me a blank cell.. no idea if it's coz i'm using Excel 2007 or not.. anyway may I just ask, what does "*|" actually mean? Thanks for accomodating my requests thus far.

@XOR LX: The code worked. Mind me asking what does REPT actually do?

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jun 26, 2014

• #8

striker359 said:

@Momentman: Your code gave me a blank cell.. no idea if it's coz i'm using Excel 2007 or not.. anyway may I just ask, what does "*|" actually mean? Thanks for accomodating my requests thus far.

@XOR LX: The code worked. Mind me asking what does REPT actually do?

Click to expand...

You're welcome.

REPT repeats a character a specified number of times. It's used here to extend the single spaces between the words to larger ones (of length 113, in my case), which is fundamental to the working of this solution.

Regards

 

Upvote 0

S

striker359

Board Regular

Joined

Jun 19, 2014

Messages

64

• Jun 26, 2014

• #9

@XOR LX: Came into another snag... unfortunately some of the data cells had strikethroughs but the code returns the value without the strikethroughs... this I'm not surprised.. but is there by any chance, possible for the codes to return cell formatting as well??

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jun 26, 2014

• #10

striker359 said:

@XOR LX: Came into another snag... unfortunately some of the data cells had strikethroughs but the code returns the value without the strikethroughs... this I'm not surprised.. but is there by any chance, possible for the codes to return cell formatting as well??

Click to expand...

No. Not without VBA.

Regards

 

Upvote 0