Data with both text and number - how to work with this? - Solved

[BirkaH]

I found a solution but i don't know how to close it 🙂

Hi.

Our items numbers consist of both numbers and text, like this 

20000009000S

20000009000M

20000009000L

It's the same items but in different sizes, so cost price, sales price and other thing are different. I would like to Merge cost prices from one sheet with inventory values in another sheet, but I can't because the items with both numbers and text have ERROR on them. 

I hope you have some good suggestions. 

Regards Birka.

[MAwwad]

One approach you could take is to create a new column in both sheets that extracts the numeric portion of the item number. You can then use this new column as the key to merge the two sheets.

For example, in Power Query Editor, you could use the "Add Column" tab and select "Custom Column". Then use a formula like this:

= Text.Select([ItemNumber],{"0".."9"})

This will create a new column that contains only the numeric portion of the item number. You can then repeat this process for the other sheet, and then use these new columns as the key to merge the two sheets.

Alternatively, if you are using Excel, you could use the "Text to Columns" feature to split the item number into two separate columns: one containing the numeric portion and one containing the text portion. You can then use the numeric portion as the key to merge the two sheets.

[BirkaH]

Thank you. But the problem is I need to have the S M and L as well (or something else), as the price, stock quantities and so on are different, as it is not the same product. 

[serpiva64]

Hi,

you have to choose type text and i think it will work (if the type has changed automatically delete the step and choose the right one).

[BirkaH]

I still have the error if the data type is tezt.