Splitting a text in a column

[RingoSun]

Inside a column I have values such as:

Column

Test Nice Product

Test 1 Product

Test 2 Good Product

Test 3 Product

I want to do the splitting inside the same column (I don't want new columns to be created) so it should look like this

Column

Test

Test 1

Test 2

Test 3

In other words, I want the words after Test to be excluded. How do I do that?

[mahenkj2 · Accepted solution]

I have tried to use certain transfomations in Power query, Please let me know if this is what you are looking for.

I will try to explain the working later:

https://drive.google.com/file/d/1oweJyDKNJ5JqK6UNTIrWxPRCUhyb7fFX/view?usp=sharing

[mahenkj2]

Hello @RingoSun

Though, I have attached a sample file for you as an example, it really also depends how your column data looks like. If there is certainty that there shall be some numbers after which you finally want to split, and only after second space you need this split (first split is between the 'Test' and numbers), then this sample file should be showing exactly that.

If your data has some other behavior, even then it should be possible to undertstand the possibilities of Power query for all those varying needs.

So what I did here:

1. Split the column at left most delimiter, i.e. a space here.

2. Split the second column again at left most delimiter i.e.a space.

3. The way your data is, first record is Test without a number, but other record have numbers. So column generated in step 2 above have text as well as numbers. We need to repace text in this column with blank. So I used Text.Select function.

4. Finally merged 2 columns of interest and removed unnecessary columns.

If any questions, please do let us know.

[jaipal]

@RingoSun there are multiple ways to do it. One of them is as follows

[RingoSun]

@jaipal 

I've been trying to experiment with that option but I just cant seem to get my desired output

[jaipal]

@RingoSun because rows has data with many spaces. You can try @Ashish_Mathur 's solution or you can try with DAX something like:

column= Left/right('table[column]', 6]   

[Ashish_Mathur]

Hi,

In the Query Editor, select this column and click on Replace Values.  Find for  Product i.e. space and Product.  Click on Replace.