May 01 2019 04:07 AM
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
148 | 111 | |
42 | 43 | 56 |
77 | 121 | |
44 | 45 | 74 |
66 | 109 | 84 |
79 | 65 | 95 |
42 | 101 | 130 |
of course, with out cutting and pasting....
thanks allot!
Roy
May 01 2019 05:00 AM
@Roy_lavi , you may use Data->Text to Columns on ribbon
May 01 2019 05:02 AM
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!
May 01 2019 05:20 AM
Solution
Hi, it worked well on most cells, thanks!
I wonder what is different in the cells it did not work on
May 01 2019 05:33 AM
@Roy_lavi , could you give the sample of the cell on which it doesn't work?
May 01 2019 05:35 AM
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.
May 01 2019 05:54 AM
@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.
May 01 2019 05:59 AM
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
May 01 2019 06:05 AM
@Roy_lavi , thank you for the update. So far so good.
May 01 2019 06:36 AM
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
May 01 2019 06:40 AM
If you had to do this for a big quantity of cell, you could do the following:
Then, apply the previous process to column B.
May 01 2019 06:41 AM
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
May 01 2019 06:44 AM
will it work for text too?
now I am referring to text. actually it's two lists of chemical compounds
May 01 2019 06:51 AM
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)))
The formula to apply to column A is the same by switching As with Cs and vice-versa.
May 01 2019 06:57 AM
@Roy_lavi , another formula for the rule is
=COUNTIF($A:$A,$B1)
assuming the range in column B starts from B1
May 01 2019 07:11 AM
@Roy_lavi , with copy/paste from PDF you may combine both steps. For example, in PDF you have
which you paste to Excel (just Ctrl+V)
It looks like
first value is number. Here click Ctrl and select
Here on second step select Comma and click Finish.
That's all, you have what you'd like to have
May 01 2019 07:13 AM
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 |
May 01 2019 07:21 AM
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.
May 01 2019 07:25 AM
@Roy_lavi , from my point of view it works correctly (I mean COUNTIF), please check attached
May 01 2019 05:20 AM
Solution
Hi, it worked well on most cells, thanks!
I wonder what is different in the cells it did not work on