Question: problem in dividing column A of both sheets into separate cells

Hi,

try these..

a) use find and replace option to find [=>] and replace :

b) use text to column option to split the data.

c) try using concatenate function.

Hi and welcome to the forum

could you supply a few sample answers of what you want please?

Hi and thanks for the welcome.

well i need the problem settled. without checking it yourself (excel file called "file" as per attachment") it is impossible to believe that it cannot be settled via text to columns / concatenate.

we do work a lot with columns / concatenate, but last time we suffered such a problem was in 2006 and we cannot remember anymore how it was settled.

basically if you open the attached excel file you will discover the main problem:

line 2 of page A / B does show the complete data:

e.g.:

"Engine type[=>]Petrol Engine Engine code[=>]FXDA Number of cylinders[=>]4 Valves per cylinder[=>]4 Brake type[=>]Disc/Drum Braking system[=>]Hydraulic Fuel[=>]Petrol Type of catalyst[=>]with 3 way catalyst Drive type[=>]Front Wheel Drive Drive[=>]Manual T"

but if you look on the line a little bit above, just right to ƒx then you will discover the dilemma:

the biggest part of the data disappeared, the only data written there: Engine type[=>]Petrol Engine

any kind of text to columns / concatenate operation resulted in loosing all data but Engine type[=>]Petrol Engine

after finishing the complete operation (content of column A of both sheets 1 and 2 in separate cells - each word in a separate cell - & content of each separate cell then to be united again).

Hope this helps, please ask if still any question and looking very much forward to hearing soon from you with the solution.

sorry, will post better resolution asap in a couple of seconds

here again with better resolution

problem.better.resolution.jpg

please upload a sample workbook, not a picture (exclude any sensitive info)

Yes i saw the file you uploaded, and I asked for some sample answers (post #4). Until you supply that, I am unable so see what you are trying to do

thanks.

still i don`t get the point what "sample answers" you expect.

I have uploaded sample file, i have added image as well, explained what the problem is, i have explained what is the desired result.

I was told that there is a line break inside each cell and i doubt there could be a formula inside the column or file, because any typical excel operation (divide data, concatenate data) leeds to the loss of
the majority of the data.

without checking it yourself (excel file called "file" as per attachment") it is impossible to believe that it cannot be settled via text to columns / concatenate.

we do work a lot with columns / concatenate, but last time we suffered such a problem was in 2006 and we cannot remember anymore how it was settled.

basically if you open the attached excel file you will discover the main problem:

line 2 of page A / B does show the complete data:

e.g.:

"Engine type[=>]Petrol Engine Engine code[=>]FXDA Number of cylinders[=>]4 Valves per cylinder[=>]4 Brake type[=>]Disc/Drum Braking system[=>]Hydraulic Fuel[=>]Petrol Type of catalyst[=>]with 3 way catalyst Drive type[=>]Front Wheel Drive Drive[=>]Manual T"

but if you look on the line a little bit above, just right to ƒx then you will discover the dilemma:

the biggest part of the data disappeared, the only data written there: Engine type[=>]Petrol Engine

any kind of text to columns / concatenate operation results in loosing all data but "Engine type[=>]Petrol Engine" after finishing the complete operation (content of column A of both sheets 1 and 2 in separate cells - each word in a separate cell - & content of each separate cell then to be united again). The words and numbers of each cell must be divided because we cannot use them actually, because the content shall appear in online shop in such manner:

Engine type[=>]Petrol Engine
Engine code[=>]FXDA
etc. etc.

line by line

but if we upload the actual data we end up everything been written in one single line.

we need to separate the words and numbers of the cells to enter <br /> command in between the data and by substituting "[=>]" with ":"

Engine type: Petrol Engine<br />Engine code: FXDA<br />Number of cylinders: 4<br />Valves per cylinder: 4<br />Brake type: Disc/Drum<br />Braking system: Hydraulic<br />Fuel: Petrol<br />Type of catalyst: with 3 way catalyst<br />Drive type:Front Wheel Drive<br />Drive: Manual T

please inform if you need more info or if i still didn`t explain it in such manner that it can be understood ?

thanks very very much in advance for your help !

regards

solved by a friendly gentleman from NJ and a mate from TX in another forum - thank God !


Use Find & Replace dialog box:

1. Options: NO checkmark in checkbox "Match entire cell contents"
2. Click cursor in "Find what" and press ALT+0010 (use number pad only !) the same time and release both keys the same time
3. looks like nothing happened, but if you look carefully you barely see the top of the blinking text cursor
4. then enter any command into "Replace with" and click "Replace all"