Forum Discussion
Split cells
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..
13 Replies
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.
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
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.
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:
Sir can we do with the help of formula?
If yes,
please help..??
Here is a attached file
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 SergeiBaklan 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.
- Thank you for the response sir.
But I want according to screenshot -
Kant Str. (should be in Name column)
46 (should be in Age column)
Berlin (should be in Place column )
Germany(should be in Country column)
Please help...??In english
How To Split Text By Space/Comma/Delimiter In Excel?
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)
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.
