Home

Separate red and black into two columns

%3CLINGO-SUB%20id%3D%22lingo-sub-819781%22%20slang%3D%22en-US%22%3ESeparate%20red%20and%20black%20into%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819781%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127999i800B783CAD19016C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture22.PNG%22%20title%3D%22Capture22.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20please%20advise%20me%20how%20can%20I%20separate%20red%20and%20black%20into%20two%20columns%20in%20excel%20.%20I%20have%20attached%20here%26nbsp%3B%20screen%20shot.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%26nbsp%3B%3C%2FP%3E%3CP%3EShikir23%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-819781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E1%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822878%22%20slang%3D%22en-US%22%3ERe%3A%20Separate%20red%20and%20black%20into%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822878%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396651%22%20target%3D%22_blank%22%3E%40Shikir23%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20could%20be%20accomplished%20with%20a%20UDF%20(User-Defined%20Function)%20in%20VBA.%20See%20how%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2234-excel-extract-cell-color.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2234-excel-extract-cell-color.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828614%22%20slang%3D%22en-US%22%3ERe%3A%20Separate%20red%20and%20black%20into%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828614%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20PReagan%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20suggestion%2C%20I%20was%20able%20to%20separate%20red%20and%20black%20using%20the%20formula.%20But%20I%20don't%20know%20how%20to%20delete%20the%20remaining%20red%20colour%20text%20in%20excel%20so%20that%20I%20can%20align%20both%20separated%20data%20into%20two%20columns%20.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129060i2E0821293AF84C4D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture23.PNG%22%20title%3D%22Capture23.PNG%22%20%2F%3E%3C%2FSPAN%3E%20I%20have%20attached%20here%20screenshot.%20Can%20you%20please%20help%20me%20on%20this%20matter%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%26nbsp%3B%3C%2FP%3E%3CP%3EShikir23%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829119%22%20slang%3D%22en-US%22%3ERe%3A%20Separate%20red%20and%20black%20into%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829119%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396651%22%20target%3D%22_blank%22%3E%40Shikir23%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20suggest%20creating%20a%20new%20UDF%20with%20the%20same%20code%20but%20instead%20of%20using%20vbRed%2C%20use%20vbBlack.%20Then%20you%20can%20extract%20all%20of%20the%20black%20text%20from%20the%20first%20column%20and%20place%20it%20into%20the%20column%20to%20the%20right%20of%20your%20UDF-vbRed%20column.%20Then%2C%20if%20all%20looks%20correct%2C%20you%20can%20delete%20all%20columns%20prior%20to%20your%20UDF-vbRed%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E
Shikir23
New Contributor

Hi, Capture22.PNG

 

Can anyone please advise me how can I separate red and black into two columns in excel . I have attached here  screen shot. 

 

Best wishes, 

Shikir23 

3 Replies

Hello @Shikir23,

 

This could be accomplished with a UDF (User-Defined Function) in VBA. See how here:

https://www.extendoffice.com/documents/excel/2234-excel-extract-cell-color.html

 

Hope this helps!
PReagan

Hi PReagan, 

 

Thank you very much for your suggestion, I was able to separate red and black using the formula. But I don't know how to delete the remaining red colour text in excel so that I can align both separated data into two columns .Capture23.PNG I have attached here screenshot. Can you please help me on this matter? 

 

Best wishes, 

Shikir23

Highlighted

Hello @Shikir23,

 

I would suggest creating a new UDF with the same code but instead of using vbRed, use vbBlack. Then you can extract all of the black text from the first column and place it into the column to the right of your UDF-vbRed column. Then, if all looks correct, you can delete all columns prior to your UDF-vbRed column.

 

Hope this helps!

PReagan

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies