0
I intend to extract only the numbers in red for the following table "Red", but the spaces are different and I have tested a lot of formulas to solve this issue.
I used a rather complex formula, but it did not work, all because the spaces between the red numbers are different, I used this formula:
=ARRUMAR(DIREITA(ESQUERDA(SUBSTITUIR(ARRUMAR(A207);" ";REPT(" ";10));30);50))
How do I extract only the numbers in red??
1
Do the following:
Insert the code below into a module. Alt+F11, Insert, Module.
Function ExtrairTextoPorCor(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
xValue = pRange.Text
For i = 1 To VBA.Len(xValue)
'O RGB determina a cor que a fonte está, caso esteja diferente, faça a alteração no trecho do código RGB(255,0,0)
If pRange.Characters(i, 1).Font.Color = RGB(255, 0, 0) Then
xOut = xOut & VBA.Mid(xValue, i, 1)
End If
Next
ExtrairTextoPorCor = xOut
End Function
Go back to the worksheet and look for an empty cell and enter the formula =Extrairtextoporcor(A207)
managed to implement the solution?
Yeah, it worked out! Only one thing that bothers a little is that, if you have for example two numbers with equal colors, ex: 111 00 111, let’s assume that these two "111" are in red, in this case it will extract like this: "111111" without giving the space between them. But if you format the spacing in red it obeys and extracts the space, "111 111"
Use this code
0
Function ExtrairTextoPorCor(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
xValue = pRange.Text
For i = 1 To VBA.Len(xValue)
'O RGB determina a cor que a fonte está, caso esteja diferente, faça a alteração no trecho do código RGB(255,0,0)
If pRange.Characters(i, 1).Font.Color = RGB(255, 0, 0) Then
xOut = xOut & VBA.Mid(xValue, i, 1)
ElseIf pRange.Characters(i, 1).Text = " " Then
xOut = xOut & VBA.Mid(xValue, i, 1)
End If
Next
ExtrairTextoPorCor = xOut
End Function
Browser other questions tagged excel
You are not signed in. Login or sign up in order to post.
But what is the logic to know which number needs to extract, would be what follows a sequence without repeating?
– Murilo Portugal
I am at work and here I am without time, but you can do with VBA using the MID function merged with this code here https://superuser.com/questions/1067320/check-excel-cell-if-font-is-a-certain-color
– Evilmaax
I wanted only the numbers in red according to each referent cell. In case there are ones that are repeated, but if I change to another type of numbering, they have to be organized according to position and quantity
– Elienay Junior
@ Max knows very little about VBA, but I know how to edit codes and record macros. I can wait, in case you want to prepare a code or a formula even in Excel
– Elienay Junior
through formula is impossible
– Evilmaax
What you want to extract is formatted as red or you just put as red to indicate what you want to extract?
– anonimo
@anonimo I just painted in red to indicate what I want to extract
– Elienay Junior
See if the logic is as follows: eliminate the eventual 11 if it occurs at the beginning of the string and then take everything from the first occurrence of 1 to the space that follows it.
– anonimo
@anonimo This formula that I posted she does almost that, but the numbers have to be one below the other no matter the amount and it works in that case! But when the numbers meet separately, not the right way!
– Elienay Junior
If you could use this same formula and increase something more, it would be good! But the other above user said it was impossible with formulas, it would be possible only if using VBA
– Elienay Junior
Honestly: I do not understand what such a formula does. For me it is meaningless.
– anonimo
@anonimo The formula extract values in sequence with the values of the lines, which comes later, test it and change the numbering "10,30,50" to regulate what you want to display in the extraction of some text, name, surname, references of some product etc...
– Elienay Junior