How To Separate Data

Hewlett-packard / 6120
July 30, 2010 at 00:43:47
Specs: Windows XP, 3.0/2GB
I would like to know if there is any way i could separate the following data using any excel formules, etc so that i could have only the EMAIL address in ONE CELL
I have already tried "find and replace" (but dont know how to FIND UPPER case) and "text to columns" fucntion but to no avail

and so on....

many thanks

See More: How To Separate Data

Report •

July 30, 2010 at 04:14:26
Your post is a little confusing. It's hard to tell what is the email address and what isn't.

Are you trying to split the data at the point where the uppercase letters change to lower case?

All of your examples have the same number of characters. Is this really the case? If so, the RIGHT and LEFT functions should work for you.

Report •

July 30, 2010 at 04:59:10
well actually i need to remove/separate the UPPER case data from the email address..
All of them are different (some with 5 UPPER case letter and some with 6 , 7, etc)

and i need just this ( without the UPPER case letter)

If only i could find the UPPER case from each of the cell and them replace it with "nothing", then it can be removed...but still struggling

Report •

July 30, 2010 at 05:41:09
You can't loop through the characters in a cell with any built-in Excel functions, but you can do it with a User Defined Function (UDF)

1 - Use Alt-F11 to open the VBA editor
2 - Insert a Standard Module (Insert...Module)
3 - Paste this code into the pane that opens
4 - Enter =NoUCase(A1) in a cell, assuming is in A1. Adjust as required.

Option Explicit
Function NoUCase(ByVal Target As Range)
Dim numChar As Integer
'Loop through string
  For numChar = 1 To Len(Target.Value)
'Compare Characters (Upper Case vs. Lower Case)
   If UCase(Mid(Target.Value, numChar, 1)) <> _
            Mid(Target.Value, numChar, 1) Then
'If they don't match, we've reached the
'end of the Upper Case letters, so return shortened string
        NoUCase = Right(Target.Value, Len(Target.Value) - numChar + 1)
        Exit Function
  End If
End Function

Report •

Related Solutions

July 30, 2010 at 06:23:17
Wow!! it works ...thanks so much DerbyDad...much appreciated!!!

Report •

July 30, 2010 at 06:31:12

Report •

Ask Question