Cookies tgħinna jagħti servizzi tagħna. Bl-użu tas-servizzi tagħna, inti taqbel li l-użu tagħna ta 'cookies.
Tip: Lingwi oħra huma tradotti minn Google. Tista 'żżur il - English verżjoni ta 'din ir-rabta.
log in
x
or
x
x
reġistru
x

or

Kif tiġbed it-test ibbażat fuq il-kulur tat-tipa minn ċellola f'Excel?

Jekk għandek lista tad-dejta b'xi test aħmar f'kull ċellula f'Excel kif muri taħt il-screenshot, u tkun taf kif tista 'tiġbed it-test aħmar biss? Issa se nintroduċa mod veloċi biex issolvi l-puzzle li test ta 'estratti bbażat fuq il-kulur tat-tipa minn ċellola f'Excel.

doc-extract-text-color-1

Test ta 'estrazzjoni bbażat fuq il-kulur tat-tipa minn kull ċellula


vleġġa bużżieqa tal-lemin blu Test ta 'estrazzjoni bbażat fuq il-kulur tat-tipa minn kull ċellula


Fl-Excel, tista 'tuża biss il-funzjoni definita biex tneħħi t-test ibbażat fuq il-kulur tat-tipa.

1. Istampa Alt + F11 ċwievet flimkien biex tiftaħ it-tieqa Microsoft Visual Basic għal Applikazzjonijiet.

2. ikklikkja Daħħal > Moduli u kopja s-segwitu tal-kodiċi VBA għat-tieqa li nfetħet.

VBA: Test ta 'estrazzjoni bbażat fuq il-kulur tat-tipa

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. Imbagħad issalva u tagħlaq id-djalogu, u agħżel ċellula vojta ħdejn il-lista tad-dejta, ikteb din il-formula = GetColorText (A1) (A1 jindika ċ-ċellula li trid estratt it-test minn), agħfas Ikteb Ikklikkja biex tikseb it-test meħtieġ, imbagħad ittella 'l-autofill jimmaniġġa biex timla l-formula sal-firxa li trid.

Issa tista 'tara t-test aħmar kollu jiġi estratt.

doc-extract-text-color-2

Tip: Fil-kodiċi VBA ta 'hawn fuq, jekk teħtieġ li tneħħi t-test iswed miċ-ċelloli, tista' tbiddel l-isem tal-kulur Kulur = vbRed għal Kulur = vbBlack.


Artikli relattivi:



Għodda tal-Produttività Rakkomandata

Uffiċċju Tab

star tad-deheb 1 Ġib tabs handy għal Excel u softwer ieħor tal-Uffiċċju, bħal Chrome, Firefox u Internet Explorer ġdid.

Kutools għal Excel

star tad-deheb 1 Amazing! Żid il-produttività tiegħek f'minuti 5. M'għandekx bżonn xi ħiliet speċjali, ħlief sagħtejn kuljum!

star tad-deheb 1 300 Karatteristiċi Ġodda għall-Excel, Għamla Excel Ħafna Faċli u Qawwija:

  • Merge Cell / Ringieli / Kolonni mingħajr ma titlef id-Data.
  • Għaqqad u Tikkonsolida Folji Multipli u Kotba tax-Xogħol.
  • Qabbel ir-Ranges, Kopja Multipla Ranges, Ikkonverti Test sa Data, Unità u l-Konverżjoni Munita.
  • Għadd bil-Kuluri, Subtotali tal-Pejġing, Sort Avvanzat u Iffiltra Super,
  • Iktar Agħżel / Daħħal / Ħassar / Test / Format / Rabta / Kumment / Kotba tax-Xogħol / Worksheets Għodda ...

Screenshot ta 'Kutools għal 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 · 2 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 · 2 months ago
    thanks, very interesting code.
  • To post as a guest, your comment is unpublished.
    Roberto · 3 months ago
    dimenticavo io ho Excel 2010
  • To post as a guest, your comment is unpublished.
    Roberto · 3 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