LinkBack URL
About LinkBacks
Hello, I would like the data from cell 2 to be separated Excell__001.JPGin columns
For instance in one column to be the A1 second column x1=520... data and the third column Y1=391...
How this can be done?
Hello, I would like the data from cell 2 to be separated Excell__001.JPGin columns
For instance in one column to be the A1 second column x1=520... data and the third column Y1=391...
How this can be done?
Use 'Data' and then 'Text to Columns'
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Can you please make me a screenshot of where are the buttons?
maybe
Δεδομένα - Κείμενο σε στήλες
btw. I never heard of XL2017 version
johnmal.png
Last edited by sandy666; 12-27-2022 at 07:16 AM.
Re: Excell cell data to the column.
=TRANSPOSE(FILTERXML("<M><R>"&SUBSTITUTE(SUBSTITUTE(A2,",","")," ","</R><R>")&"</R></M>","//R"))
Type this formula into cell B2 and copy down.
Credit for this should go to the folks on this topic
excelforum.com/excel-general/1396295-spilit-text-to-columns.html#post5771677
and to a wonderful youtube video I found yesterday
youtube.com/watch?v=ibcbh8ep3Is
This introduced me to the concept of the main door and room for FILTERXML.
Fantastic!
Hope this helps.
W.
Data in colum A, then in B
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,",","",1)," ",REPT(" ",100)),(COLUMNS($A$1:A$1)-1)*100+1,99))
Copy across and down
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks a lot can you please me make me a correction to copy only value data the second and third column not ''x1'' and ''ψ1'', Ι want only numbers.Originally Posted by Wayneskie
the result after text to columns (Alt+A+E) looks like this
Al Xl=520136.964 Yl=3912463.269 A2 X2=520134.004 Y2=3912459.874 A3 X3=520131.852 Y3=3912457.465 A4 X4=520129.977; Y4=3912453.299 A5 X5=520126.361 Y5=3912449.398 A6 X6=520124.929; Y6=3912446.461 A7 X7=520124.371 Y7=3912442.777 A8 X8=520121.014 Y8=3912437.906 A9 X9=520117.419; Y9=3912433.734
t2c.png
Last edited by sandy666; 12-27-2022 at 09:33 AM.
Can we change the formula in order to copy only numbers after X1 AND Ψ1 not to include x1=, ψ1=
Αlso i try to copy the formula but did not worked to other rows.Attachment 810875Attachment 810876
=(TEXTSPLIT(TEXTSPLIT(TRANSPOSE(FILTERXML("<M><R>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","")," X1=","</R><R>")," Y1=","</R><R>")&"</R></M>","//R")),"=")," "))
Paste into Cell B2 and then copy down
Attachment 810881
Can you help me ?
I'm afraid your attachments are invalid
have a look at this - Formula lives in cell B2. Drag this down to capture all your data
@Wayneskie
User updated XL version to 2007 so I think your formula (post#10) won't work
I think i did not work maybe I must purchase the latest version?
As when I copy it appears the followingAttachment 810886
Ah OK, I would recommend microsoft 365, then you will always be up to date
@johnmal
try this to attach your file
with Quick Reply
hta.png
then use Preview to see your attachment and then Submit Reply
Excell__004.JPG
See new attached.
but this is a picture not an EXCEL FILE !
I think the problem is that you do not have access to the functionality I have used, since I created this formula using Microsoft 365 Excel.
Please Login or Register to view this content.
Last edited by JohnTopley; 12-27-2022 at 10:54 AM.
see attached file
position and number of characters are hardcoded
maybe someone else will give you a proper solution
Thanks a lot for your help sandy666 and JohnTopley you deserve at least a drink when you came to Crete!
You're welcome and thank you for the rep.
Maybe:
=TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE($A2,",","")," ",REPT(" ",125)),125*COLUMNS($A:A),125))
copied across and down
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
maybbe uzo ?
Hello, again I would like a small help in an Excel file that I want to separate numbers (P,X,Y,D).
Any assistance will be grateful.
Similar to the formula Glen wrote for you last year at this time, try:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks