# Extract the last characters in a cell.

#### quemuenchatocha

##### New Member
Dear cordial greetings

I am learning the basics of Excel's VBA language, and I have come across the issue related to the default color range. Investigating in different sources I have found a code that allows to obtain the values for the RGB primary color palette, presenting them all together [Column C], or separately [Columns D:F] (the credits of the function correspond to Mr. Allen Wyatt, Determining the RGB Value of a Color).

Now, my question is directed to obtain the values recorded in column C, separately in columns H:J.

I have already obtained the respective values for column H (Color "R") and column I (Color "H"), through the implementation of combined functions, but I have found it difficult to obtain the last value for Color "B", which goes in column J.

Cell Formulas
RangeFormula
C2:C27C2=getRGB1(A2)
D2:D27D2=getRGB2(A2,1)
E2:E27E2=getRGB2(A2,2)
F2:F27F2=getRGB2(A2,3)
H2:H27H2=NUMBERVALUE(MID(LEFT(C2,FIND(",",C2)-1),FIND("(",C2)+1,LEN(C2)))
I2:I27I2=NUMBERVALUE(MID(C2,FIND(",",C2)+1,FIND(",",C2,FIND(",",C2)+1)-FIND(",",C2)-1))

I have tried various combinations with the functions RIGHT, MID, FIND, but have not obtained any satisfactory result. I apologize in advance for the length of the statement, and I appreciate any advice regarding how to obtain these last characters of the cell.

P.S.: In case the VBA functions are needed, here are the codes I use

VBA Code:
``````Sub ColorRef()

Dim x As Integer

For x = 1 To 56

If x <= 57 Then

ActiveCell(x, 1).Interior.ColorIndex = x

ActiveCell(x, 2) = x

End If

Next x

End Sub``````

VBA Code:
``````Function getRGB1(rcell) As String

Dim ColorVal As Long

Dim R As Long

Dim G As Long

Dim B As Long

ColorVal = rcell.Interior.Color

R = ColorVal Mod 256

G = ColorVal \ 256 Mod 256

B = ColorVal \ 65536 Mod 256

getRGB1 = "GRB(" & R & "," & G & "," & B & ")"

End Function``````

VBA Code:
``````Function getRGB2(rcell As Range, Optional opt As Integer) As Long

Dim ColorVal As Long

Dim R As Long

Dim G As Long

Dim B As Long

ColorVal = rcell.Interior.Color

R = ColorVal Mod 256

G = ColorVal \ 256 Mod 256

B = ColorVal \ 65536 Mod 256

If opt = 1 Then

getRGB2 = R

ElseIf opt = 2 Then

getRGB2 = G

ElseIf opt = 3 Then

getRGB2 = B

Else

getRGB2 = ColorVal

End If

End Function``````

#### quemuenchatocha

##### New Member
Also, you can try this formula in J2:

=LOOKUP(999,-("("&RIGHT(C2,{2,3,4})))
Thank you for your collaboration, I will put your formula into practice.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### quemuenchatocha

##### New Member
Just for fun.

New__Document (11) (version 1).xlsb
ABCDEFG
2ColorColor IndexRGBRGB
31GRB(0,0,0)0 0 0
42GRB(255,255,255)255 255 255
53GRB(255,0,0)255 0 0
64GRB(0,255,0)0 255 0
75GRB(0,0,255)0 0 255
86GRB(255,255,0)255 255 0
97GRB(255,0,255)255 0 255
108GRB(0,255,255)0 255 255
119GRB(128,0,0)128 0 0
1210GRB(0,128,0)0 128 0
1311GRB(0,0,128)0 0 128
1412GRB(128,128,0)128 128 0
1513GRB(128,0,128)128 0 128
1614GRB(0,128,128)0 128 128
1715GRB(192,192,192)192 192 192
1816GRB(128,128,128)128 128 128
1917GRB(153,153,255)153 153 255
2018GRB(153,51,102)153 51 102
2119GRB(255,255,204)255 255 204
2220GRB(204,255,255)204 255 255
2321GRB(102,0,102)102 0 102
2422GRB(255,128,128)255 128 128
2523GRB(0,102,204)0 102 204
2624GRB(204,204,255)204 204 255
2725GRB(0,0,128)0 0 128
2826GRB(255,0,255)255 0 255
Sheet6
Cell Formulas
RangeFormula
E3:G28E3=LET(t," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(C3,LEN(C3)-3),"(",""),")",""),","," ")&" ",s,SEQUENCE(,3),su,SUBSTITUTE(t," ","~",s),fi,FIND("~",su,s),f,FIND("~",SUBSTITUTE(t," ","~",s+1),s),l,LEFT(t,f),REPLACE(l,1,fi,""))
Dynamic array formulas.
You are very cordial for your explanation, although your formula is outside my basic knowledge, I will try to understand it and put it into practice, a hug!

#### quemuenchatocha

##### New Member
A slightly shorter version of my suggestion in post#6
Excel Formula:
``=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(MID(C2,FIND("(",C2)+1,100),",","</m><m>"),")","")&"</m></k>","//m"))``
As always I appreciate your immense help, I would like to ask you another favor, would you be so kind to explain me a little, how your formula works? Again, thank you very much for your collaboration.

#### quemuenchatocha

##### New Member
Also, you can try this formula in J2:

=LOOKUP(999,-("("&RIGHT(C2,{2,3,4})))
Could I ask you another favor, would you be so kind to explain me a little bit how your formula works, especially the part of the comparison vector where you add a - sign and concatenate it with the RIGHT function. Thanks again!

#### Fluff

##### MrExcel MVP, Moderator

This part `MID(C2,FIND("(",C2)+1,100)` find the ( in the cell & returns everything after it to give `"0,0,0)"`
then the substitutes substitute the comma for the tags & removes the ) giving `"0</m><m>0</m><m>0"`
That is then concatenated with the starting & ending tags to give `"<k><m>0</m><m>0</m><m>0</m></k>"`
The Filterxml functions then converts that to an array, which is transposed so that the array spills horizontally, rather than vertically.

HTH

#### Michael M

##### Well-known Member
Glad we could ALL make a contribution....

#### quemuenchatocha

##### New Member
This part `MID(C2,FIND("(",C2)+1,100)` find the ( in the cell & returns everything after it to give `"0,0,0)"`
then the substitutes substitute the comma for the tags & removes the ) giving `"0</m><m>0</m><m>0"`
That is then concatenated with the starting & ending tags to give `"<k><m>0</m><m>0</m><m>0</m></k>"`
The Filterxml functions then converts that to an array, which is transposed so that the array spills horizontally, rather than vertically.

HTH
Thank you very much for your clarification, I will put your explanations into practice.
A big hug!

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback.

Replies
1
Views
125
Replies
7
Views
82
Replies
3
Views
203
Replies
7
Views
220
Replies
1
Views
100

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back