How to split string text and numbers into different columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi I have a data here that shows the combination of string and numbers. I want to separate or split the two different characters into different column:
Input |
122220 Property Equipment |
122330 Office- Equipment |
Expected Output1 | Expected Output2 |
122220 | Property Equipment |
122330 | Office- Equipment |
Can you help me resolve this one pls? Thank you
- Labels:
- Adobe
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
1. Use the RegEX tool
2. In the Column to Parse, select the Input column
3. In the regular expression (\d)\s(.*)
4. Use the Output Method as Parse
In the Output Columns, modify the Column name from RegExOut1 to Expected Output1 and RegExOut2 to Expected Output2 or any other desired column names
Shanker V
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hello than you for this, how about this one?
Input |
5100130 AR Penalty/Interest credit clearing |
5100140 AR CWT clearing |
5100140 AR CWT clearing |
5100160 Cinema cards payable |
I find it hard to separate these characters using regex tool. Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
For your second input shared also, my output will work.
1. Use the RegEX tool
2. In the Column to Parse, select the Input column
3. In the regular expression (\d)\s(.*)
4. Use the Output Method as Parse
In the Output Columns, modify the Column name from RegExOut1 to Expected Output1 and RegExOut2 to Expected Output2 or any other desired column names
In the line number 3, I am splitting digits and other characters followed by space.
Shanker V
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello I tried your workflow but this is the result:
The digits will become "0"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
After you used this Regex and split into 2 columns
For the second column it might contain the Space in front, use the data cleansing tool to remove the blanks in front.
Hope this helps.
Shanker V
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Use this below.
(\d*)\s(.*)
For the second column it might contain the Space in front, use the data cleansing tool to remove the blanks in front.
If you are ok with the space, then not an issue. You don't need to remove the space created in front of the column 2.
Hope this helps.
Shanker V
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hope the previous solution worked.
1. One way is the old one, where you parse and use the Data cleansing tool to remove the whitespaces.
2. Way 2, try this
(\d*)\s\s\s\s(.*)
As there is 4 spaces between the digits and the letters you have shared in the sample, this will generate the accurate output which you are looking for.
The () used to create new columns.
\d* - helps to read the digits
\s helps to skip the spaces where we don't want to consider
.* helps to read the remaining characters after the space
Many thanks
Shanker V
![](/skins/images/D34B41DA407DC996E7BFF253AD24F7E2/responsive_peak/images/icon_anonymous_message.png)