help in putting each value in a cell

[Marwah]

I have attached the part of my data , I want the upper part to look like the one in the bottom

so for example I have the following in one cell
A1BG rs11433683 rs58916940 rs8109095 rs11669136
rs4078160 rs4619097 rs10821846 rs61856571

and I can't use the text to columns space separation because this will make it in two lines and I want it like the following

A1BG rs11433683 rs58916940 rs8109095 rs11669136 rs60909809

so they are in different cells in one line not two lines

Thank you,

[sabha]

I have attached the part of my data , I want the upper part to look like the one in the bottom

so for example I have the following in one cell
A1BG rs11433683 rs58916940 rs8109095 rs11669136
rs4078160 rs4619097 rs10821846 rs61856571

and I can't use the col to text space separation because this will make it in two lines and I want it like the following

A1BG rs11433683 rs58916940 rs8109095 rs11669136 rs60909809

so they are in different cells in one line not two lines

Thank you,

I did not understand your question properly.
Why don't you try Date->Text to Columns -> Delimited by space?

[Marwah]

I did it didn't work, can you please apply it to the upper part of my data

[Marwah]

since the data is in one cell looks like this
A1BG rs11433683 rs58916940 rs8109095 rs11669136
rs4078160 rs4619097 rs10821846 rs61856571

how can I make it in one line rather than two each value in a cell
to be as following
A1BG rs11433683 rs58916940 rs8109095 rs11669136 rs4078160 rs4619097 rs10821846

[sabha]

I did it didn't work, can you please apply it to the upper part of my data

sorry but I still did not understand what exactly you want to do. Check the attached file where I have used Text To Columns

[Marwah]

this is wrong not what I want I want the output to be as follows

Please Login or Register  to view this content.

[benishiryo]

hi Marwah. not sure if you have made a mistake or i don't understand the outcome. your data in the cell has 9 values. why is your outcome having only 8? it is missing the last one, "rs61856571".

your sample excel file does not tie either. A1 is showing "A1BG rs10406132 rs1544494...."
but row 23 is showing
A1BG
rs11433683
rs58916940

if it's a typo, then maybe:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),CHAR(10),REPT(" ",LEN($A1))),1+(COLUMNS($B1:B1)-1)*LEN($A1),LEN($A1)))

[Marwah]

Those are just examples not exact, I just want the upper part to look like the below one that is all

but how to apply your code ?

[sandy666]

maybe this?

[Marwah]

Yes that is what I need plus i need a division between values in Value.1 means put them in two columns
"A1BG" rs10406132
"A1CF" rs12360073
"A2M " rs11567808
"A4GNT" rs35632604
"AAAS" rs61915662

[Marwah]

can you tell me how to do that ?

[sandy666]

Sure, use PowerQuery (Get&Transform)

btw, first part doesn't have space so it looks like : A1BGrs1040613

[Marwah]

what is PowerQuery (Get&Transform) ?

[Marwah]

there is a space , should be

[Marwah]

Can you show me the steps please

[Marwah]

by the way I use Macbook, so please tell me which tools that is available for mac to use to get the solution

[sabha]

Looks like you are getting solution. Nevertheless, I was working on your query but its not complete as i need to rush somewhere. I can give you the start...
The following code separates words and puts them in separate columns but the code is incomplete as it takes care of only one row. May be someone can complete this. If not, I will try to complete when i get back online.

Please Login or Register  to view this content.

[sabha]

Sure, use PowerQuery (Get&Transform)

btw, first part doesn't have space so it looks like : A1BGrs1040613

gr8!
I remember you helped me with my query using PQ
looks like i need to learn PQ

[Marwah]

Thank you, could you please give me the full code and tell me how to apply it to my data

[sandy666]

You've Ex2016, so you need to read bottom small text on the ribbon: Get&Transform, then select your data and click no. 1

gt.jpg

then in Query pivot column, join appropriate columns, unpivot all columns, delete unnecessary column, load to the sheet

Maybe "here" is a space but PQ doesn't see this "first" space so you need do it manually or something like that before you add it to the query

I don't know Mac so I can't help

@sabha: Learn PQ ? sure

[sandy666]

here is with first space (you can add space before you add this to the table or after all but you will need to refresh query table)

attached zip file with text file where code is
txt was too big but zip is ok.

[sabha]

Thank you, could you please give me the full code and tell me how to apply it to my data

Press Ctrl+F11
In the Microsoft Visual Basic window, click on Insert->Module
Paste the following code inside the module
From the excel file, press Ctrl+F8 and run the code CheckEveryWordStartingWithRS

You would get the solution from row # 25 onwards.

Please Login or Register  to view this content.

[Marwah]

Press Ctrl+F11
In the Microsoft Visual Basic window, click on Insert->Module
Paste the following code inside the module
From the excel file, press Ctrl+F8 and run the code CheckEveryWordStartingWithRS

You would get the solution from row # 25 onwards.

Please Login or Register  to view this content.

Does it work on MacBook?

[sabha]

Does it work on MacBook?

I am afraid I dont know. Even I am not a pro... I thought I can atleast give you start and hence wrote a broken code for you . btw, the solution suggested by @sandy666 is really cool

[Marwah]

You've Ex2016, so you need to read bottom small text on the ribbon: Get&Transform, then select your data and click no. 1

Attachment 539844

then in Query pivot column, join appropriate columns, unpivot all columns, delete unnecessary column, load to the sheet

Maybe "here" is a space but PQ doesn't see this "first" space so you need do it manually or something like that before you add it to the query

I don't know Mac so I can't help

@sabha: Learn PQ ? sure

Can you tell me the exact steps I don't know how to use the power query

[sandy666]

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

here is a good start: PowerQuery or here

[Marwah]

Thanks but I really need the steps as I don't have time to look at the PowerQuery

Thank you

[sandy666]

Open last xlsb file
Edit Query
and all steps you've there

pqsteps.jpg

btw.

I don't have time to look at the PowerQuery

if you don't have time why should I?