Sorting the first text in a row

H

Hans

I have 4 columns with names, in some rows are none to 4 names. The names
appear random in any of the columns. It could look like this:
name1 name2
name3
name 4 name5

I want to get the first name that appears (from left to right) and put it in
the fifth column, like this:
name1
name3
name4

I can't figure it out, any help is welcome
 
D

Don Guillett

One way
'=======
Option Explicit
Sub getleftmostcell()
Dim lr As Long
Dim i As Long
'finds last row
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'gets cell
For i = 1 To lr
If Len(Application.Trim(Cells(i, 1))) > 0 Then
Cells(i, 5) = Cells(i, 1)
Else
Cells(i, 5) = Cells(i, 1).End(xlToRight)
End If
Next i
End Sub
 
G

Gary Brown

Assuming the data is in columns A thru D...
=IF(LEN(A1)<>0,A1,IF(LEN(B1)<>0,B1,IF(LEN(C1)<>0,C1,D1)))
 
R

Ron Rosenfeld

I have 4 columns with names, in some rows are none to 4 names. The names
appear random in any of the columns. It could look like this:
name1 name2
name3
name 4 name5

I want to get the first name that appears (from left to right) and put it in
the fifth column, like this:
name1
name3
name4

I can't figure it out, any help is welcome


Assume your data is in A1:D1

This formula must be **array-entered**:

=INDEX(A1:D1,1,MATCH(TRUE,A1:D1<>"",0))

Then fill down as far as required.

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top