Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Split
Hi,
I have 4000 line of data which is first and surnames combined e.g DavidHill, MarkSmith etc etc. I need a formula to divide these by the second capital letter only to make David Hill, Mark Smith etc etc All entries have a capital letter at the start of the forename and surname, thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Split
Hi,
Lets say we have MarkSmith in A1. Put this array formula in B1 to extract the first name. Note it's all one line and see below for how to enter and array formula =LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&"" And then this in c1 to get the second name =MID(A1,LEN(B1)+1,LEN(A1)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "DDay" wrote: Hi, I have 4000 line of data which is first and surnames combined e.g DavidHill, MarkSmith etc etc. I need a formula to divide these by the second capital letter only to make David Hill, Mark Smith etc etc All entries have a capital letter at the start of the forename and surname, thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Split
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long, j As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To LastRow For j = Len(.Cells(i, TEST_COLUMN).Value) To 2 Step -1 If Asc(Mid$(.Cells(i, TEST_COLUMN).Value, j, 1)) <= 90 Then .Cells(i, TEST_COLUMN).Value = _ Left$(.Cells(i, TEST_COLUMN).Value, j - 1) & _ " " & Mid$(.Cells(i, TEST_COLUMN).Value, j) End If Next j Next i End With End Sub -- __________________________________ HTH Bob "DDay" wrote in message ... Hi, I have 4000 line of data which is first and surnames combined e.g DavidHill, MarkSmith etc etc. I need a formula to divide these by the second capital letter only to make David Hill, Mark Smith etc etc All entries have a capital letter at the start of the forename and surname, thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Split
With A1 having names try the below formulas...Its a bit lenghty to handle
names without last name... B1 First name () =LEFT(A1,FIND(" ",A1 & " ")-1) C1 Last name =IF(ISERROR(FIND(" ",A1)),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))) If this post helps click Yes --------------- Jacob Skaria "DDay" wrote: Hi, I have 4000 line of data which is first and surnames combined e.g DavidHill, MarkSmith etc etc. I need a formula to divide these by the second capital letter only to make David Hill, Mark Smith etc etc All entries have a capital letter at the start of the forename and surname, thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Split
I misunderstood the query..Please ignore this post...
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With A1 having names try the below formulas...Its a bit lenghty to handle names without last name... B1 First name () =LEFT(A1,FIND(" ",A1 & " ")-1) C1 Last name =IF(ISERROR(FIND(" ",A1)),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))) If this post helps click Yes --------------- Jacob Skaria "DDay" wrote: Hi, I have 4000 line of data which is first and surnames combined e.g DavidHill, MarkSmith etc etc. I need a formula to divide these by the second capital letter only to make David Hill, Mark Smith etc etc All entries have a capital letter at the start of the forename and surname, thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Split
Also look at the responses you have got to your posting in another
newsgroup. -- Regards Roger Govier "DDay" wrote in message ... Hi, I have 4000 line of data which is first and surnames combined e.g DavidHill, MarkSmith etc etc. I need a formula to divide these by the second capital letter only to make David Hill, Mark Smith etc etc All entries have a capital letter at the start of the forename and surname, thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to split up text | Excel Worksheet Functions | |||
Split a text | Excel Worksheet Functions | |||
Split text without using data-text to columns | Excel Discussion (Misc queries) | |||
Split Text | Excel Discussion (Misc queries) | |||
text cells end page how split to next. Text lost! | Excel Discussion (Misc queries) |