| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns
"Rick Rothstein" wrote: rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999)) Two things about about the above line of code.... First, you can remove the Trim function call since you accounted for the leading space when you used n+1 for the Mid function's second argument. Second, you can remove the 9999 (third) argument from the Mid function call... unlike the worksheet function's MID function, in the VBA one the third argument is optional and, when you omit it, it automatically returns all the remaining chars (after the position specified by the second argument). -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Here is a sample macro for column A split into A & B: Sub SplitThem() Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange) For Each rr In r v = rr.Value n = InStr(v, "-") rr.Value = Left(v, n - 1) rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999)) Next End Sub -- Gary''s Student - gsnu201001 "LeisaA" wrote: 500+ sentences, I need to seperate (text to columns) after the first "-" only. There are subsequent "-" but I only want it seperated at the first. And when I seperate it in the second column I do not want a space preceeding the text. Can you give advice on this, any help would be much appreciated! Thanks Sample Data AIR TAXI - An aircraft operator who conducts operations for hire or compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer passenger seats and a payload capacity of 7,500# or less. An air taxi operates on an on-demand basis and does not meet the "flight scheduled" qualifications of a commuter. AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority to promote the safe, orderly, and expeditious flow of air traffic. AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses air/ground communications, visual signaling, and other devices to provide ATC services to aircraft operating in the vicinity of an airport or on the movement area. Authorizes aircraft to land or takeoff at the airport controlled by the tower or to transit the Class D airspace area regardless of flight plan or weather conditions (IFR or VFR). A tower may also provide approach control services (radar or non-radar). ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with a layer (approximately 5.5% thickness per side) of high-purity aluminum, popularly used in airplane manufacture. ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice radio, this is the current NATO version in global use: . Sorry, I am not understanding how to use this function? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
| help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
| merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
| Linking text columns with text and data columns | Excel Worksheet Functions | |||
| extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming | |||
Hybrid Mode
