Forum Discussion
How to split text from rows to different collumns ?
Dear community, I am trying to sort a huge excel file with more than 22000 rows of data and I am a beginner when it comes to Excel.
As you can see, the data is inserted in each row separately instead of being assigned to each individual column. I would like to sort it somehow in a way which would allow me for example to extract only the latitude and longitude coordinates written at the end of each row.
I would be very grateful if anyone could give me a tutorial or some tips on how to separate it into columns.
How about this:
- Open your Excel file and select the data range you want to work with.
- Use the Text to Columns feature:
- Select the column that contains your data.
- Go to the Data tab on the Ribbon.
- Click on Text to Columns.
- Choose Delimited and click Next.
- Select the delimiter that separates your data (e.g., comma, space) and click Next.
- Choose the destination where you want the separated data to appear and click Finish.
- Extract Latitude and Longitude:
- If your latitude and longitude coordinates are at the end of each row, you can use the RIGHT and LEFT functions to extract them.
- For example, if your coordinates are in column A, you can use:
=RIGHT(A1, LEN(A1) - FIND(",", A1) - 1)
to extract the longitude, assuming the coordinates are separated by a comma.
- Sort the Data:
- Once you have the latitude and longitude in separate columns, you can sort the data by selecting the columns and using the Sort feature under the Data tab.
2 Replies
How about this:
- Open your Excel file and select the data range you want to work with.
- Use the Text to Columns feature:
- Select the column that contains your data.
- Go to the Data tab on the Ribbon.
- Click on Text to Columns.
- Choose Delimited and click Next.
- Select the delimiter that separates your data (e.g., comma, space) and click Next.
- Choose the destination where you want the separated data to appear and click Finish.
- Extract Latitude and Longitude:
- If your latitude and longitude coordinates are at the end of each row, you can use the RIGHT and LEFT functions to extract them.
- For example, if your coordinates are in column A, you can use:
=RIGHT(A1, LEN(A1) - FIND(",", A1) - 1)
to extract the longitude, assuming the coordinates are separated by a comma.
- Sort the Data:
- Once you have the latitude and longitude in separate columns, you can sort the data by selecting the columns and using the Sort feature under the Data tab.
In B2:
=--CHOOSECOLS(TEXTSPLIT(A2, ","), 6, 7)
This will return latitude in B2 and longitude in C2.
Fill down from B2.
