Extract the last characters in a cell.

quemuenchatocha

New Member
Joined
Aug 4, 2021
Messages
29
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,169
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try using
Excel Formula:
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(C2,",",REPT(" ",LEN(C2))),LEN(C2))),")","")
 
Solution

hajiali

Active Member
Joined
Sep 8, 2018
Messages
458
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
521
Office Version
  1. 2016
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)
 

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
521
Office Version
  1. 2016

ADVERTISEMENT

Also, you can try this formula in J2:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,360
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 4, 2021
Messages
29
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Aug 4, 2021
Messages
29
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,491
Messages
5,764,676
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.
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
Top