# 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``````

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Michael M

##### Well-known Member
Try using
Excel Formula:
``=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(C2,",",REPT(" ",LEN(C2))),LEN(C2))),")","")``

• quemuenchatocha

#### hajiali

##### Well-known Member
this is what I come up with not sure if there is a better way but Try this in Cell J2 and fill:

Excel Formula:
``=LEFT(TRIM(MID(C2,FIND("#",SUBSTITUTE(C2,",","#",2))+1,255)),FIND(")",TRIM(MID(C2,FIND("#",SUBSTITUTE(C2,",","#",2))+1,255)))-1)``

• quemuenchatocha

#### Phuoc

##### Well-known Member
Book1
ABCHIJ
1ColorColor IndexRGBRGB
21GRB(0,0,0)000
32GRB(255,255,255)255255255
43GRB(255,0,0)25500
54GRB(0,255,0)02550
65GRB(0,0,255)00255
76GRB(255,255,0)2552550
87GRB(255,0,255)2550255
98GRB(0,255,255)0255255
109GRB(128,0,0)12800
1110GRB(0,128,0)01280
1211GRB(0,0,128)00128
1312GRB(128,128,0)1281280
1413GRB(128,0,128)1280128
1514GRB(0,128,128)0128128
1615GRB(192,192,192)192192192
1716GRB(128,128,128)128128128
1817GRB(153,153,255)153153255
1918GRB(153,51,102)15351102
2019GRB(255,255,204)255255204
2120GRB(204,255,255)204255255
2221GRB(102,0,102)1020102
2322GRB(255,128,128)255128128
2423GRB(0,102,204)0102204
2524GRB(204,204,255)204204255
2625GRB(0,0,128)00128
2726GRB(255,0,255)2550255
Sheet1
Cell Formulas
RangeFormula
H2:J27H2=--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(\$C2,")",""),"(",","),",",REPT(" ",100)),100*COLUMNS(\$H2:H2),100)

• quemuenchatocha

#### Phuoc

##### Well-known Member
Also, you can try this formula in J2:

=LOOKUP(999,-("("&RIGHT(C2,{2,3,4})))

• quemuenchatocha

#### Fluff

##### MrExcel MVP, Moderator
Another option for 365
+Fluff 1.xlsm
ABHIJ
1ColorColor IndexRGB
21000
32255255255
4325500
5402550
6500255
762552550
872550255
980255255
10912800
111001280
121100128
13121281280
14131280128
15140128128
1615192192192
1716128128128
1817153153255
191815351102
2019255255204
2120204255255
22211020102
2322255128128
24230102204
2524204204255
262500128
27262550255
Data
Cell Formulas
RangeFormula
H2:J27H2=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",","),")",","),",","</m><m>")&"</m></k>","//m[position()>1 and position()<last()]"))
Dynamic array formulas.

• quemuenchatocha

#### lrobbo314

##### Well-known 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.

• quemuenchatocha

#### Fluff

##### MrExcel MVP, Moderator
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"))``

• quemuenchatocha

#### quemuenchatocha

##### New Member
Try using
Excel Formula:
``=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(C2,",",REPT(" ",LEN(C2))),LEN(C2))),")","")``
Thank you very much for your valuable collaboration, I will put your formula into practice.

#### quemuenchatocha

##### New Member
this is what I come up with not sure if there is a better way but Try this in Cell J2 and fill:

Excel Formula:
``=LEFT(TRIM(MID(C2,FIND("#",SUBSTITUTE(C2,",","#",2))+1,255)),FIND(")",TRIM(MID(C2,FIND("#",SUBSTITUTE(C2,",","#",2))+1,255)))-1)``
You are very kind, thank you for your valuable help!

Replies
18
Views
388
Replies
1
Views
909
Replies
5
Views
869
Replies
12
Views
294
Replies
7
Views
634

### Forum statistics

1,186,808
Messages
5,959,926
Members
438,454
Latest member
leopedrini ### 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