Add space after colored text
I am using Microsoft Excel 2013.
I have a lot of data that I need to split in Excel that is in one cell. The Text to Columns feature works great, except for one catch.
In one cell I have First Name
, Last Name
and Email address
. The last name and email address do not have a space between them, but the color of the names is different from the email address.
Example (all caps are RGB colored names (1, 91, 167), lowercase letters are just plain black text):
JOHN DOEjohndoe@acmerockets.com
So I need to put a space after the DOE for it to read:
JOHN DOE johndoe@acmerockets.com
I have about 20k lines to go through so that all tips are appreciated. I just need to get a space or something in between this last name and the email so that I can use the Text to Columns feature and separate them.
source to share
Not a complete answer, but I would do it like this:
Step 1 to get rid of formatting:
- Copy all the text you have in the notebook
- Then copy the text from Notepad to excel as text
I think this should remove all formatting issues.
Step 2 - Use VBA to Capture Emails. I am assuming you have all your letters as lowercase. So something like this should do the trick ( link link2 ):
([a-z0-9\-_+]*@([a-z0-9\-_+].)?[a-z0-9\-_+].[a-z0-9]{2,6})
Step 3 is to exclude the emails that you extracted from Step 2 from the body text. Something like this with a simple Excel function:
=TRIM(SUBSTITUTE(FULLTEXT,EMAIL,""))
Since you removed all formatting in Step 1, you can apply it back when you did
source to share
You can quickly knock this out by taking advantage of how Font
returns Color
for a set of characters that don't have the same color: it returns Null
! Knowing this, you can iterate over 2 characters at a time and find the first place where it throws Null
. Now you know that there is a color shift and can spit out shapes with Mid
.
The code uses this behavior while IsNull
iterating through a fixed one Range
. Define Range
however you want to get cells. By default, it spits them out in adjacent two columns with Offset
.
Sub FindChangeInColor()
Dim rng_cell As Range
Dim i As Integer
For Each rng_cell In Range("B2:B4")
For i = 1 To Len(rng_cell.Text) - 1
If IsNull(rng_cell.Characters(i, 2).Font.Color) Then
rng_cell.Offset(0, 1) = Mid(rng_cell, 1, i)
rng_cell.Offset(0, 2) = Mid(rng_cell, i + 1)
End If
Next
Next
End Sub
Displaying ranges and results
The best part about this approach is that the actual colors don't matter. You also don't have to manually search for the switch, although that would be the next step.
Also your adjacent cells will be blank if no color change is found, so it is decently robust against bad inputs.
Edit adds the ability to change the original line if you want this:
Sub FindChangeInColorAndAddChar()
Dim rng_cell As Range
Dim i As Integer
For Each rng_cell In Range("B2:B4")
For i = 1 To Len(rng_cell.Text) - 1
If IsNull(rng_cell.Characters(i, 2).Font.Color) Then
rng_cell = Mid(rng_cell, 1, i) & "|" & Mid(rng_cell, i + 1)
End If
Next
Next
End Sub
The image of the results again uses the same input as above.
source to share