SOLVED

moving multiple values in the same cell to a new one

Copper Contributor

Hello

I have a long list (column) of multiple values and I am looking for an easier way to cut one value or more to another cell.

In other words, I need to seperate two or more values from the same cell so that each value has it's own cell.

for example

I have this short list with two or more values in each cell 

148,111
42,43,56
77,121
44,45,74
66,109,84
79,65,95
42,101,130

 and I want to make it like this

148111 
424356
77121 
444574
6610984
796595
42101130

 

of course, with out cutting and pasting....

 

thanks allot!

 

Roy

18 Replies

@Roy_lavi , you may use Data->Text to Columns on ribbon

@Roy_lavi 

Select your column

Go to Tab Data

Click button "Text to Columns" (on Data Tools group)

On the dialogue box that pops up, choose "Delimited"

click Next

put a checkmark in "comma" (only)

click next

Click Finish

 

Let me know if this works. Good luck!

 

 

 

 

best response confirmed by Roy_lavi (Copper Contributor)
Solution

@Celia_Alves 

 

Hi, it worked well on most cells, thanks!

 

I wonder what is different in the cells it did not work on

 

 

@Roy_lavi , could you give the sample of the cell on which it doesn't work?

@Roy_lavi 

I tested here by copying the data from your post and the same happened here. It seems that some of the comas are being interpreted as the thousands separator, and not a text comma. You can see that if you select your column and format as general, or text, or number. 

 

 

@Celia_Alves , it looks like source data was copy/pasted from web. If only one comma Excel interprets it as thousand separator and paste the value as number. If few commas - as text.

 

If so, tasks becomes more interesting.

@Sergei Baklan 

 

So, the error accured in just a few cells, so I changed the commas to spaces and set the delimiters to space

 

thank you very much 

@Roy_lavi , thank you for the update. So far so good.

@Sergei Baklan 

Hi, I have another question if you don't mind

:)

 

I have in column A 250 cells with text

 

and column b about 80 cells with text

 

I'd like to know how to search column b to match column a and bold or colour the matches

@Roy_lavi 

If you had to do this for a big quantity of cell, you could do the following:

Roy.JPG

Then, apply the previous process to column B. 

 

@Sergei Baklan 

BTW

 

when I paste to excel from PDF copy (which caused the above fault) you can 'special paste' and choose 'unicode text'  it paste it just like I need it

 

@Celia_Alves 

will it work for text too?

 

now I am referring to text. actually it's two lists of chemical compounds

@Roy_lavi 

My last answer was still for your first question.

For the second one. You can use conditional formatting with "use formula to determine which cells to format"

The formula to apply to column C is 

=NOT(ISNA(VLOOKUP($C1,$A:$A,1,0)))

Roy2.JPG

 

The formula to apply to column A is the same by switching As with Cs and vice-versa.

@Roy_lavi , another formula for the rule is

=COUNTIF($A:$A,$B1)

assuming the range in column B starts from B1

 

@Roy_lavi , with copy/paste from PDF you may combine both steps. For example, in PDF you have

image.png

which you paste to Excel (just Ctrl+V)

It looks like

image.png

first value is number. Here click Ctrl and select

image.png

Here on second step select Comma and click Finish.

That's all, you have what you'd like to have

image.png

@Celia_Alves 

I think it only partly worked. 

 

 column a             

Acenanaphthene-d10 (IS)
Acenaphthene
Acenaphthylene
Acetophenone
2-Acetylaminofluorene
1-Acetyl-2-thiourea
Aldrin
2-Aminoanthraquinone
Aminoazobenzene
4-Aminobiphenyl
Anilazine
Aniline
o-Anisidine
Anthracene
Aramite
Atrazine
Azinphos-methyl (Guthion)
Azobenzene
Barban
Benzaldehyde
Benz(a)anthracene
Benzidine
Benzo(b)fluoranthene
Benzo(k)fluoranthene
Benzoic
Benzo(g,h,i)perylene
Benzo(a)pyrene
Benzo(e)pyrene
p-Benzoquinone
Benzyl alcohol
a-BHC
beta-BHC
gamma-BHC (Lindane)
d-BHC
1,1'-Biphenyl
Bis(2-chloro-1-methylethyl)ether
Bis(2-chloroethyl)ether
Bis(2-chloroethoxy)methane
Bis(2-ethylhexyl)phthalate
Bromoxynil (Brominal)
4-Bromophenyl phenyl ether
Butyl benzyl phthalate
Caprolactam
Captafol
Captan
Carbaryl (Sevin)
Carbazole
Carbofuran (Furaden)
Carbophenothion
Chlorfenvinphos
4-Chloroaniline
Chlorobenzilate
5-Chloro-2-methylaniline
4-Chloro-3-methylphenol
1-Chloronaphthalene
2-Chloronaphthalene
2-Chlorophenol
4-Chlorophenyl phenyl ether
4-Chloro-1,2-phenylenediamine
4-Chloro-1,3-phenylenediamine
Chrysene
Chrysene-d12 (IS)
Coumaphos
p-Cresidine
Crotoxyphos
2-Cyclohexyl-4,6-dinitrophenol
4,4'-DDD
4,4'-DDE
4,4'-DDT
Demeton-O
Demeton-S
Diallate (cis or trans)
2,4-Diaminotoluene
Dibenz(a,j)acridine
Dibenz(a,h)anthracene
Dibenzo(a,e)pyrene
Dibenzofuran
1,2-Dibromo-3-chloropropane (DBCP)
Di-n-butyl phthalate
Dichlone
1,2-Dichlorobenzene
1,3-Dichlorobenzene
1,4-Dichlorobenzene
1,4-Dichlorobenzene-d4 (IS)
3,3'-Dichlorobenzidine
2,4-Dichlorophenol
2,6-Dichlorophenol
Dichlorovos (DDVP,Dichlorvos)
Dicrotophos
Dieldrin
Diethyl phthalate
Diethyl sulfate
Diethylstilbestrol
Dimethoate
3,3'-Dimethoxybenzidine
Dimethyl aminoazobenzene
Dimethyl phthalate
7,12-Dimethylbenz(a)anthracene
3,3'-Dimethylbenzidine
2,4-Dimethylphenol
α,α-Dimethylphenylamine
1,2-Dinitrobenzene
1,3-Dinitrobenzene (1,3-DNB)
1,4-Dinitrobenzene
4,6-Dinitro-2-methylphenol
2,4-Dinitrophenol
2,6-Dinitrophenol
2,4-Dinitrotoluene
2,6-Dinitrotoluene
Dinocap
Dinoseb (DNBP)
Di-n-octyl phthalate
1,4-Dioxane
5,5-Diphenylhydantoin
1,2-Diphenylhydrazine
Diphenylamine
Disulfoton
Endosulfan I
Endosulfan II
Endosulfan sulfate
Endrin
Endrin aldehyde
Endrin ketone
EPN
Ethion
Ethyl carbamate
Ethyl methanesulfonate
Famphur
Fensulfothion
Fenthion
Fluchloralin
Fluoranthene
Fluorene
2-Fluorobiphenyl (surr)
2-Fluorophenol (surr)
Heptachlor
Heptachlor epoxide
Hexachlorobenzene
Hexachlorobutadiene
Hexachlorocyclopentadiene
Hexachloroethane
Hexachlorophene
Hexachloropropene
Hexamethylphosphoramide (HPMA)
Hydroquinone
Indeno(1,2,3-cd)pyrene
Isodrin
Isophorone
Isosafrole
Kepone
Leptophos
Malathion
Maleic anhydride
Mestranol
Methapyrilene
Methoxychlor
Methyl methanesulfonate
Methyl parathion
3-Methylcholanthrene
4,4'-Methylenebis(2-chloroaniline)
4,4'-Methylenebis(N,N-dimethyl-aniline)
1-Methylnaphthalene
2-Methylnaphthalene
2-Methylphenol
3/4-Methylphenolc
Mevinphos
Mexacarbate
Mirex
Monocrotophos
Naled
Naphthalene
Naphthalene-d8 (IS)
1,4-Naphthoquinone
1-Naphthylamine
2-Naphthylamine
Nicotine
5-Nitroacenaphthene
2-Nitroaniline
3-Nitroaniline
4-Nitroaniline
5-Nitro-o-anisidine
Nitrobenzene
Nitrobenzene-d5 (surr)
4-Nitrobiphenyl
Nitrofen
2-Nitrophenol
4-Nitrophenol
4-Nitroquinoline-1-oxide
N-Nitrosodi-n-butylamine
N-Nitrosodiethylamine
N-Nitrosodimethylamine
N-Nitrosodiphenylamine
N-Nitrosodi-n-propylamine
N-Nitrosomethylethylamine
N-Nitrosomorpholine
N-Nitrosopiperidine
N-Nitrosopyrrolidine
5-Nitro-o-toluidine
Octamethylpyrophosphoramide
4,4'-Oxydianiline
Parathion
Pentachlorobenzene
Pentachloronitrobenzene
Pentachlorophenol
Perylene
Perylene-d12 (IS)
Phenacetin
Phenanthrene
Phenanthrene-d10 (IS)
Phenobarbital
Phenol
Phenol-d6 (surr)
1,4-Phenylenediamine
Phorate
Phosalone
Phosmet (Imidan)
Phosphamidon
Phthalic anhydride
2-Picoline
Piperonyl sulfoxide
Pronamide (Kerb)
Propylthiouracil
Pyrene
Pyridine
3-(Chloromethyl)pyridine hydrochloride
Resorcinol
Safrole
Strychnine
Sulfallate
Terbufos
Terphenyl-d14 (surr)
1,2,4,5-Tetrachlorobenzene
2,3,4,6-Tetrachlorophenol
Tetrachlorvinphos (Stirophos,Gardona)
Tetraethyl dithiopyrophosphate
Tetraethyl pyrophosphate (TEPP)
Thionazine
Thiophenol (Benzenethiol)
2,4-Toluene diisocyanate
o-Toluidine
Toxaphene
2,4,6-Tribromophenol (surr)
1,2,4-Trichlorobenzene
2,4,5-Trichlorophenol
2,4,6-Trichlorophenol
O,O,O-Triethyl phosphorothioate
Trifluralin (Treflan)
Trimethyl phosphate
2,4,5-Trimethylaniline
1,3,5-Trinitrobenzene
Tris(2,3-dibromopropyl)phosphate
Tri-p-tolyl phosphated

 

column b 

 

Pyridine
N-Nitrosodimethylamine
Phenol
Aniline
Bis(2-chloroethyl)ether
2-Chlorophenol
1,3-Dichlorobenzene
1,4-Dichlorobenzene
Benzyl alcohol
1,2-Dichlorobenzene
2-Methylphenol (o-cresol)
2,2'-oxybis(1-chloropropane)
3-Methylphenol (m-cresol)
4-Methylphenol (p-cresol)
N-Nitroso-di-n-propylamine
Hexachloroethane
Nitrobenzene
Isophorone
2-Nitrophenol
2,4-Dimethylphenol
Bis(2-chloroethoxy)methane
2,4-Dichlorophenol
1,2,4-Trichlorobenzene
Naphthalene
4-Chloroaniline
Hexachlorobutadiene
4-Chloro-3-methylphenol
2-Methylnaphthalene
1-Methylnaphthalene
Hexachlorocyclopentadiene
2,4,6-Trichlorophenol
2,4,5-Trichlorophenol
2-Chloronaphthalene
2-Nitroaniline
1,4-Dinitrobenzene
Acenaphthylene
1,3-Dinitrobenzene
Dimethylphthalate
2,6-Dinitrotoluene
1,2-Dinitrobenzene
Acenaphthene
3-Nitroaniline
2,4-Dinitrophenol
Dibenzofuran
2,4-Dinitrotoluene
4-Nitrophenol
2,3,4,6-Tetrachlorophenol
2,3,5,6-Tetrachlorophenol
Fluorene
4-Chlorophenyl phenyl ether
Diethylphthalate
4-Nitroaniline
4,6-Dinitro-2-methylphenol (Dinitro-o-cresol)
Diphenylamine
Azobenzene
4-Bromophenyl phenyl ether
Hexachlorobenzene
Pentachlorophenol
Phenanthrene
Anthracene
Carbazole
Di-n-butylphthalate
Fluoranthene
Pyrene
Benzyl butyl phthalate
Bis(2-ethylhexyl)adipate
Benz(a)anthracene
Chrysene
Bis(2-ethylhexyl)phthalate
Di-n-octyl phthalate
Benzo(b)fluoranthene
Benzo(k)fluoranthene
Benzo(a)pyrene
Indeno(1,2,3-cd)pyrene
Dibenz(a,h)anthracene
Benzo(g,h,i)perylene

 

column c

 

TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
TRUE
TRUE
FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
TRUE
FALSE
TRUE
FALSE
TRUE
FALSE
FALSE
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE
FALSE
FALSE
FALSE
TRUE
TRUE
FALSE

@Roy_lavi 

By mistake, my example had columns A and C when it should be A and B. So, in the formulas, replace C with B.

 

The formulas do not go into cells, they go in the dialogue box of conditional formatting.

Select column A > Home tab > Conditional Formatting button > New Rule> select "use formula to determine..." > enter the formula for the column A there > click format > choose the format you want to happen when the formula is true.

 

Then select column B and do the same, switching the formula accordingly.

 

@Roy_lavi , from my point of view it works correctly (I mean COUNTIF), please check attached

1 best response

Accepted Solutions
best response confirmed by Roy_lavi (Copper Contributor)
Solution

@Celia_Alves 

 

Hi, it worked well on most cells, thanks!

 

I wonder what is different in the cells it did not work on

 

 

View solution in original post