how to use power query split part of number

[Oscarr]

hi

anyone know how to use power query split part of number? pls refer attachment

Thanks

 

[Hui]

=VALUE(RIGHT(D6,5)) without power query

 

[Oscarr]

=VALUE(RIGHT(D6,5)) without power query

Hi @Hui ,

Thanks for help, but your formula doesn't work.

or you get wrong that meaning?

i need to split that red colour number...

 

[Oscarr]

Hi @Chihiro

did you know what to make?

 

[GraH - Guido]

1. Load from table
2. define as text data type
3. split by number of characters (PQ to my knowledge does not see colour), like Hui suggested via formula.
4. transforms to this...

 

[Oscarr]

1. Load from table
2. define as text data type
3. split by number of characters (PQ to my knowledge does not see colour), like Hui suggested via formula.View attachment 49275
4. transforms to this...

View attachment 49276

Hi @GraH - Guido ,

this is what i want... thanks for your help

 

[Hui]

My formula does exactly that ?
You may need to adjust the input cell to your data?

It takes the right 5 characters

GraH's solution also takes the right 5 characters

If only 4 are red, both will fail

There is no way of selecting color with PQ or Formula
It can be done using VBA

 

[GraH - Guido]

Hi @Hui ,

Thanks for help, but your formula doesn't work.

or you get wrong that meaning?

i need to split that red colour number...

Could it be that you need to use ";" between the arguments of the function instead of ","?

 

[Hui]

If you want to get the red part of a string that isn't 5 characters long try:

Function Get_Red(ByRef target As Variant) As String
'Get Red part of string
Dim i As Integer
For i = 1 To Len(target)
    If target.Characters(Start:=i, Length:=1).Font.Color = 255 Then
        Get_Red = Right(target, Len(target) - i + 1)
        Exit Function
    End If
Next
End Function

To use it copy and paste the code into a Code Module in VBA
in Excel =Get_Red(A1)

 

[Oscarr]

Could it be that you need to use ";" between the arguments of the function instead of ","?

i use your first time give solution...my problem is done...

Thanks