Text to column #2

Is there a quick way to separate the below column into 
letter and part#(hundreds of rows)? 

from: 

IC19-004-01
TRS30-000-01
C12-001-01
27-044-01

To:
IC   19-004-01
TRS  30-000-01
C    12-001-01
     27-044-01
0
10/2/2003 9:44:58 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
483 Views

Similar Articles

[PageSpeed] 35

Hi,

Not the most elegant solution I'm afraid, but it appears to work.

if your list starts in A1, then in B1 one enter:

=IF(ISNUMBER(VALUE(MID(A1,1,1))),1,IF(ISNUMBER(VALUE(MID(A1,2,1))),2,IF(ISNUMBER(VALUE(MID(A1,3,1))),3,IF(ISNUMBER(VALUE(MID(A1,4,1))),4))))

in c1 enter 

=LEFT(A1,B1-1)

and in d1 enter

=RIGHT(A1,LEN(A1)-B1+1)

Highlight cells b1, c1 and d1 and drag down the page by pulling on the
bottom right hand corner of the selection.

Hope I understood your problem correctly.

Gromit



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/3/2003 12:12:45 AM
In your sample data, all the parts end with a nine digit number. So, if 
the part numbers start in cell A2, enter the following formula in cell 
B2:  =LEFT(A2,LEN(A2)-9)

Enter the following in C2: =RIGHT(A2,9)

Copy the two formulas down to the last row of data.



Eileen wrote:
> Is there a quick way to separate the below column into 
> letter and part#(hundreds of rows)? 
> 
> from: 
> 
> IC19-004-01
> TRS30-000-01
> C12-001-01
> 27-044-01
> 
> To:
> IC   19-004-01
> TRS  30-000-01
> C    12-001-01
>      27-044-01


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/3/2003 1:18:52 AM
Reply:

Similar Artilces:

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

ADO recordset command text vs command
Hi all, I've noticed a slight difference between the way recordset command text works vs commands themselves where SP calls are concerned. We have many calls to SP which also include input and output parameters. When examining a SQL Server trace, for example, the difference is, that we can specify command text to an ADO recordset, which includes input parameter names. However, the ADO command object does not appear to specify any parameter names, although it does support return of output parameters. Is there a way for the ADO command object to also specify the parameter names? This would ...

Outlook 2003 sends my gmail through 2 steps, why??
I am using Outlook 2003 to send my gmail account through smtp.gmail.com. When I send mail in Outlook, it goes into the Outbox rather than get sent right away. Then from my outbox, I can send the email. This seems like a redundancy in efforts, how can I get Outlook to send it straight away? Look at the Tools Menu, then Options, then Mail Setup Tab. Check off the box "Send immediately when connected". -- Lenny V "Uncle Scotty" wrote: > I am using Outlook 2003 to send my gmail account through > smtp.gmail.com. When I send mail in Outlook, it goes into the Outbox...

How do you stop text size increasing in email replies?
I am a new Outlook user and finding it rather frustrating that every time I reply to an email and whenever the recipient responds to me, the text size in each email going back in the email trail gets larger and larger. Does anyone know how to stop this from happening? ...

Adding a column in Excel 2007
Have a column with letters in it. Want to sum up how many of one letter I have in the column. What is a formula to do this please To count the number of cells that equal X =countif(a:a,"X") To count the number of cells that have at least one X in them: =countif(a:a,"*X*") nip wrote: > > Have a column with letters in it. Want to sum up how many of one > letter I have in the column. What is a formula to do this please -- Dave Peterson On Dec 10, 10:44=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote: > To count the number of cells that eq...

how can text be set up to generate a number?
i would like to a word generate a number, i.e.> enter "apples"in one cell and excel generate "352" in a different cell for items like inventory. Basically You will need to set up a 'Lookup' table somewhere with Apples 352 In two columns. Complete this for all entries and then if you are going to enter 'Apples' on another sheet in A1 then in A2 enter =VLOOKUP(A1,Your_Lookup_Range_Address,2,FALSE) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "jwmaes" <jwmaes@discussions.microso...

Text Boxes added to Chart Series
How do I keep text boxes that I've added to a data series with the column they belong on? If I change the size of the chart in anyway, I have to move the text boxes back to their respective spots on the chart. Text boxes are not added to a series, but to the chart. This prevents their sticking to any particular points. Could you add them as data labels? If you use a built-in position (i.e., don't drag them around) they will stick with their associated points - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://P...

Text-wrapping horizontal axis labels
Hi, I've a small problem which I can't seem to find a solution for - I have created a set of financial reports for a client, which includes a number of charts. The charts are waterfall charts, which I've constructed using a form of stacked bar chart. The labels for the horizontal axis are linked to text alongside the calculations for the charts. The text in the labels is of varying lengths and for some of the charts, this text is being wrapped to two layers on the label that appear on the chart. Every now and then, one of the labels is too long, and instead of ...

Can't see text box fill colour
I have recently reloaded Office Small Business following a hard drive crash. I made some changes to display in Control Panel Accessibility this time that weren't there before. I've changed back to default but the problem continues. Previously I could fill or border text boxes and see the results in both normal view and print preview. Now the fill/border colour is only visible in print preview. Borders appear as black and fills as white in normal view. Text colours are unaffected. This is making life difficult as white text in a black fill just disappears until I go to pr...

Microsoft Excel ASC II Text Files
How do I convert an Excel spreadsheet into this type of file in order to import to another program such as "MaiList & AddressBook" Hi Bill try saving as 'CSV' file in the Save As dialog. This is a comma separated text (ASCII) file HTH Frank Bill Cadwallader wrote: > How do I convert an Excel spreadsheet into this type of > file in order to import to another program such > as "MaiList & AddressBook" ...

Outlook hanging #2
When I open Outlook it is slow to check send/receive. Once open, it appears to be randomly hanging when I click on a message to open it. Especially those with attachments. I have to control, alt, delete to move on. When I open the details window in the error report window it has "szAppName:OUTLOOK.EXE szAppVer: 10.0.4510.0 szModName:hungapp szModVer: 0.0.0.0 offset:00000000". I can't seem to get past this problem, and have been unable to find anything to help me fix it. ...

Outlook 2000 & Vista #2
Can anyone tell me whether Outlook 2000 (from Ofice 2000) is compatible with Vista? If so, where might I find support to complete the installation? I keep getting an "Operation Cancelled" message anytime I try to sign in or set Outlook 2000 as my default. Thanks -- Steve Only 2003 and above is fully compatible OL2k is out of support as is OLxp "Steve" <Steve@discussions.microsoft.com> wrote in message news:ACAE99AB-0EAB-4523-9304-AFAB18AB724C@microsoft.com... > Can anyone tell me whether Outlook 2000 (from Ofice 2000) is compatible > with > Vista? I...

CheckBox #2
I have a sheet with a checkbox in column A and a value in column B. There are about 30 rows of this data. The user selects the items they want to view and I run VBA code based on their selection. I want to turn the checkboxes on and off within VBA but can't figure out what each checkbox is named and how to reference it in VBA code. I just need to do something like: CheckBox13 = True Checkbox 99 = False like in Access Can anyone help please? Thanks in advance. Is ActiveSheet.CheckBox13 = True what you need ? Regards.. Daniel "PeterM" <PeterM@discussions.microsoft...

Date formatting in Excel #2
How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I type 10-4, Excel returns Oct 05. Hi When no year is typed, excel would assume it is the current year. You would ahve to type the full date (including 2004) for it to display as Oct 2004. Use Format - cells - date and then select the particular format you want from the options there. >-----Original Message----- >How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I >type 10-4, Excel returns Oct 05. >. > ...

Sales Pipeline Report #2
Anybody had problems with this report in terms of the group by Date function. Cannot get the date order of the report to work in terms of ordering by Estimated Close date. Am posting here in the attempt to get anyone out there to help me please... have been pulling my hair out in looking at the query in this report to work out how to order by Estimated Close Date! Thanks in advance, Charlie Absolutely. The CRM 4.0 Sales Pipeline report group by Date does not work correctly when using non-US CRM date formats. I have logged this as an issue with MSFT support, who accept there is a ...

Installed from CD, apps in "trial" mode #2
I went through the whole procedure again. Here is the list of files in the Trash: com.microsoft.Office.prefs.plist com.microsoft.Excel.prefs.plist com.microsoft.Entourage.prefs.plist com.microsoft.Entourage.plist com.microsoft.OfficeNotifications.plist Office Font Cache (11) Microsoft Office ACL [English] OLE Registration Database 11 OfficeSync Prefs Custom Dictionary Office 11 First Run Entourage Preferences Proofing Tool Preferences Office Registration Cache 11 OK, the Remove Office tool is operating correctly, there's something else wrong. Check back in a few days, I'm escalatin...

Viewing 2 worksheets in the same workbook
Is there a way of viewing data on two separate worksheets in the same workbook in a similar way to splitting a single worksheet? Thanks Choose Window> New Window Choose Window> Arrange Select Tile (or one of the other options), and check the box 'Windows of active workbook' Click OK In each of the windows, select a different worksheet to view. Ant wrote: > Is there a way of viewing data on two separate worksheets in the same > workbook in a similar way to splitting a single worksheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures....

Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have the problem that I am having. I will post my macro in this, for anyone that is interested. My users get a csv file every month, and we have to clean it up. This macro does that. My last issues are this: 1) having the spreadsheet create page breaks whenever the value in column B changes. Below is just that code. Code: col = 2 LastRw = ActiveSheet.UsedRange.Rows.Count For X = 2 To LastRw If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then ActiveWindow.SelectedSheets.HPageBreaks...

Mailbox Manager and Moved Mailboxes #2
I set up a new exchange 2003 server and moved mailboxes over to it from an old exchange 5.5 server. Then I ran mailbox manager on the 2003 server in Report Only mode. The report came back all zeros. The only policy I set up was a 30 day limit on deleted items, and I know there were deleted items in the mailboxes I moved older than that. Does it have anything to do with the fact that I just moved the mailboxes recently, so they haven't been on this particular server for 30 days? I read that Mailbox Manager uses three dates to see if it should process a message- PR_MESSAGE_DELIVERY_TI...

Pass a command text in pivot table
I've got a pivot table with data give back by odbc (in excel 2003). When i pass a new sql command, it answer me the database, also if i pass the connection string. My code is this: With ActiveSheet.PivotTables(sNamePivot).PivotCache .CommandText = sSelect .Connection = "ODBC;DSN=DS;DB=" & NomeDB & ";SRVR=;UID=SYSADM;PWD=" .Refresh End With sNamePivot --> string variable contain the correct name of the pivot table. NomeDB --> string variable contain the database. sSelect --> variable contain the sql command. Why does it called me ...

Excel: When printing some cells will not print text in them
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) When I print an Excel 08 spread sheet, two of the cells that have text in them will not print the text in the cell. All the other cells with text prints fine. The text does not even show up on print preview in the print dialogue box. Any suggestions on how to get it to include the text in these two cells? ...

Find and loop help-multiple columns
I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Fi...

Finding text
I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. It's not real clear what you want to do. Here's my best guess... Assume you want to know if John is in the range. =IF(COUNTIF(A1:A100,"John"),"John","not found") Better to use a cell to hold the criter...

New personal finance resource website open #2
--_NextPart_00009772-00002116-10DD2067-6005 Content-Type: text/plain Content-Transfer-Encoding: 7bit New Personal Finance Resource Website Open http://www.your-personal-finances.com/ - Free articles and information on personal finance --_NextPart_00009772-00002116-10DD2067-6005-- *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** ...

MultiCurrency Setup #2
created an exchange rate table HKD TO CNY. I'm stuck on #7, HKD TO CNY not available from the Exchange Table window screen. Here's what I have done so far. 1. Setup currencies (setup-system-currency) 2. Assign company access to currencies (setup-system-multicurrency access) 3. Setup exchange rate tables (setup-system-exchange table) 4. Assign exchange rates (cards-system-exchange table) 5. Assign company access to exchange rates (setup-system-multicurrency) 6. Set Multicurrency defaults to a company (setup-financial-multicurrency) 7. Assign rate type to rate tables (setup-financial-...