Extract the last characters in a cell.

quemuenchatocha

Board Regular
Joined
Aug 4, 2021
Messages
50
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

Thank you for your attention.

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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try using
Excel Formula:
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(C2,",",REPT(" ",LEN(C2))),LEN(C2))),")","")
 
Upvote 0
Solution
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)
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top