| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return cell characters after space | Excel Worksheet Functions | |||
| Inserting a space between characters in a cell | Excel Discussion (Misc queries) | |||
| Put A Space Between 2 right characters | Excel Worksheet Functions | |||
| Count Characters with space in a cell | Excel Discussion (Misc queries) | |||
| cHARACTERS BEFORE THE SPACE | Excel Worksheet Functions | |||
![T De Villiers[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
