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 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
Joined
Aug 4, 2021
Messages
29
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Aug 4, 2021
Messages
29
Office Version
  1. 365
  2. 2019
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"))
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
Joined
Aug 4, 2021
Messages
29
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

quemuenchatocha

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