Characters before the space
 

Hi,

I need a function which does the following:

F1 FRED
F23 GHTYRFFFF
G1234 AD

becomes:
F1
F23
G1234

i.e Returns the characters before the space, many thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=502474

Characters before the space
 

Try =LEFT(A1,FIND(" ",A1)-1)
This question belongs in the Worksheet.Function group.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"T De Villiers"
wrote in message
news:T.De.Villiers.21u6s0_1137598209.1181@excelfor um-nospam.com...

Hi,

I need a function which does the following:

F1 FRED
F23 GHTYRFFFF
G1234 AD

becomes:
F1
F23
G1234

i.e Returns the characters before the space, many thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=502474

Characters before the space
 

In another column try this:

=LEFT(A1,FIND(" ",A1)-1)

This assumes that your data starts in A1.

HTH,
Paul
"T De Villiers"
wrote in message
news:T.De.Villiers.21u6s0_1137598209.1181@excelfor um-nospam.com...

Hi,

I need a function which does the following:

F1 FRED
F23 GHTYRFFFF
G1234 AD

becomes:
F1
F23
G1234

i.e Returns the characters before the space, many thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=502474

Characters before the space
 

Hi T,

You have a couple of function solutions but if you want a programming
solution try this.

Sub SpaceRanger()
Dim Lrow As Long
Dim Drng As Range

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Drng = Range("A1:A" & Lrow)

Drng.TextToColumns Destination:=Range("A1")

Range("B:B").ClearContents
End Sub

Where your data is in A1 to A: whatever and you have column B available.

HTH
Regards,
Howard

"T De Villiers"
wrote in message
news:T.De.Villiers.21u6s0_1137598209.1181@excelfor um-nospam.com...

Hi,

I need a function which does the following:

F1 FRED
F23 GHTYRFFFF
G1234 AD

becomes:
F1
F23
G1234

i.e Returns the characters before the space, many thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=502474