Jun 07 2021 02:41 AM - edited Jun 07 2021 03:10 AM
Hello Everyone,
Suppose this is a data. And I want to split cell with the help of formula.
It should be split like this -
Please help.??
Here is a attached file..
Jun 07 2021 03:00 AM
Here are two different suggested solutions.
Steps are in German, but as a .gif file you can easily see the way. Fished from the German Internet, I didn't know exactly from where, probably from some public conversation.
The other suggested solution is in the inserted file.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Jun 07 2021 03:08 AM
Jun 07 2021 03:24 AM
In english
If you have a column list of data and you want to split them into several columns by a specific delimiter just like the below screenshots shown, how can you split them in Excel? Maybe some of users think of the Text to Column function only, but now I will introduce not only Text to Columns function, but also a VBA code
Help for self-help.
I am a simple user, who simply offered his knowledge and time voluntarily, with no guarantee.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Jun 07 2021 03:39 AM - edited Jun 07 2021 03:43 AM
@Excel You start by saying "Suppose this is the data". That could mean that your real data isn't like in your screenshots at all. But let's suppose it is, then you could go through a number of Text-to-column steps, manually, or you use Power Query (PQ). The attached file contains a PQ solution.
First split by the comma. This will create three columns:
Then split the first column at the point where a character changes to a number (digit):
Then add a custom column that excludes any number from the field that contains the city:
Trim the custom column, delete the original column with the city information and reorder the remaining columns:
Load the end result back to Excel:
Jun 07 2021 03:47 AM
As variant that could be
with
=TRANSPOSE(FILTERXML("<r><n>" & SUBSTITUTE(REPLACE(A2,SEARCH(" ", A2, SEARCH(" ",A2)+1),1,","),",","</n><n>") & "</n></r>", "//n"))
but it very depends on source text logic.
And that's an array formula.
Jun 07 2021 03:59 AM - edited Jun 07 2021 04:05 AM
Please let me confirm have you done with the help of Transpose formula?
Sir can you split cell same thing with the help of SUBSTITUTE function with TRIM function???
But it should be dynamic..
Please help..???
Here is a attached file
Jun 07 2021 04:12 AM
First we replace every second space with comma. After that FILTERXML() splits text prepared by SUBSTITUTE() using commas as separators. TRANSPOSE() only shows result in the row, not in column.
If you are on Excel which support dynamic arrays (365) it works as it is. Otherwise array formula with Ctrl+Shift+Enter shall be used.
Jun 07 2021 04:15 AM - edited Jun 07 2021 04:16 AM
Jun 07 2021 04:28 AM
When it works as it is. Just open the file attached to my post to check.
Jun 07 2021 04:29 AM
Jun 07 2021 04:31 AM - edited Jun 07 2021 04:48 AM
Sir can we do with the help of formula?
If yes,
please help..??
Here is a attached file
Jun 07 2021 07:33 AM
@Excel Not sure what you are after. The file you attached is the same as earlier. I provided a Power Query solution that does exactly what you asked for, and @Sergei Baklan provided a formula solution. The only difference is that his formula does not exclude the numbers for entries like "10786 Berlin". Perhaps Sergei can fix that, as I'm not all that familiar with FILTERXML.