Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Kouman ekstrè tèks ki baze sou koulè font ki sòti nan yon selil nan Excel?

Si ou gen yon lis done ak kèk tèks wouj nan chak selil nan Excel jan yo montre anba a anba a, epi ou konnen ki jan yo ekstrè tèks la wouj sèlman? Koulye a, mwen pral entwodui yon fason rapid yo rezoud devinèt la ki Ekstrè tèks ki baze sou koulè a ​​font ki sòti nan yon selil nan Excel.

doc-ekstrè-tèks-koulè-1

Ekstrè tèks ki baze sou koulè font ki soti nan chak selil


flèch ble jarèt dwat Ekstrè tèks ki baze sou koulè font ki soti nan chak selil


Nan Excel, ou sèlman ka itilize fonksyon an defini ekstrè tèks la ki baze sou koulè font.

1. Peze Alt + F11 kle ansanm yo louvri Microsoft vizyèl Debaz la pou aplikasyon pou fenèt.

2. Klike sou Antre > Modil epi kopye kòd VBA swivan fenèt la.

VBA: Ekstrè tèks ki baze sou koulè font

Function GetColorText(pRange As Range) As String
	'Updateby20141105
	Dim xOut As String
	Dim xValue As String
	Dim i As Long
	xValue = pRange.Text

	For i = 1 To VBA.Len(xValue)

		If pRange.Characters(i, 1).Font.Color = vbRed Then
			xOut = xOut & VBA.Mid(xValue, i, 1)
		End If

	Next

	GetColorText = xOut
End Function

3. Lè sa a, sove epi fèmen dyalòg la, epi chwazi yon selil vid akote lis done a, tape fòmil sa a = GetColorText (A1) (A1 endike selil la ou vle ekstrè tèks soti nan), peze Antre nan kle jwenn tèks ki nesesè yo, Lè sa a, trennen manch autofill ranpli fòmil la nan seri a ou vle.

Koulye a, ou ka wè tout tèks wouj yo ekstrè.

doc-ekstrè-tèks-koulè-2

Ide: Nan kòd ki pi wo a VBA, si ou bezwen ekstrè tèks nwa soti nan selil, ou ka chanje non an koulè Koulè = vbRed yo Koulè = vbBlack.


Atik relatif:



Rekòmande Zouti Productivity

Biwo Tab

lò star1 Pote onglè sou lamen pou Excel ak lòt biwo lojisyèl, jis tankou Chrome, Firefox ak nouvo Internet Explorer.

Kutools pou Excel

lò star1 Etonan! Ogmante pwodiktivite ou nan minit 5. Pa bezwen okenn ladrès espesyal, sove de zè de tan chak jou!

lò star1 300 karakteristik nouvo pou Excel, fè Excel anpil fasil ak pwisan:

  • Rantre selilè / ranje / Kolòn san yo pa pèdi done.
  • Konbine ak Konsolide Pliye Plizyè ak Manyèl.
  • Konpare Range, Kopi plizyè chif, Konvèti tèks nan dat, inite ak konvèsyon lajan.
  • Konte pa Koulè, Paging subtotals, Avanse Triye ak Super Filter,
  • Plis Chwazi / Insert / Efase / Tèks / Fòma / Link / Kòmantè / Workbooks / Worksheets Zouti ...

Piki ekran nan Kutools pou Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    prudwi · 3 months ago
    The code works well if the red strings are continuous. In case they are separated in the source cell, they are glued to each other in output cell. I mean there is no space in the output if the red text strings are away from each other. Can you please provide a solution for this?
  • To post as a guest, your comment is unpublished.
    Kazam Raza · 3 months ago
    thanks, very interesting code.
  • To post as a guest, your comment is unpublished.
    Roberto · 4 months ago
    dimenticavo io ho Excel 2010
  • To post as a guest, your comment is unpublished.
    Roberto · 4 months ago
    Buonasera, grazie mille un'ottima funzione. Però ho un problema, quando val nel foglio ed inserisco es. = GetColorText (A1) mi restituisce il valore giusto però come riavvio il file mi da errore #VALORE!, se poi clicco 2 volte sopra mi da il valore corretto e così via. che posso fare?
  • To post as a guest, your comment is unpublished.
    JM · 1 years ago
    Hey, Thanks for the Tip. Works great with Red and Black.
    What if I want to parse "Purple"? changing it vbPurple does not work.
    Thanks