Text #3

In Cell A1,2 ,3 ect i have the following info

12345BO
1234BO
123456BO

How do i convert these in Cells B1, 2, 3 etc to :
012345
001234
123456

To create a 6 figure number with the BO dropped off?
0
DavaSutts (5)
12/3/2004 12:15:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
829 Views

Similar Articles

[PageSpeed] 47

in column b put this formula
find(a1,"BO",1)
in column c put this formula
mid(a1,1,b1-1)

format the c column to numbers

"Dava Sutts" wrote:

> In Cell A1,2 ,3 ect i have the following info
> 
> 12345BO
> 1234BO
> 123456BO
> 
> How do i convert these in Cells B1, 2, 3 etc to :
> 012345
> 001234
> 123456
> 
> To create a 6 figure number with the BO dropped off?
0
12/3/2004 12:25:04 PM
One way (if you don't need the letters at all anymore):
Make ure there are  blank cells to the right of thses cells.
Select the cells
Go to Data | Text to Columns
Choose Delimited and click Next
Set B as the Delimiter
Click Finish
You'll end up with the Numbers in their original cell, the B will be gone 
and the O will be in a cell to the right. You can delete the extraneous O's.

Then, with your number cells Selected:
Format | Cells
On the Number Tab Choose Custom
Type 6 consecutive zeroes (000000) in the line provided. Don't use the 
parentheses. This will give you the number format you typed out below.

tj 


"Dava Sutts" wrote:

> In Cell A1,2 ,3 ect i have the following info
> 
> 12345BO
> 1234BO
> 123456BO
> 
> How do i convert these in Cells B1, 2, 3 etc to :
> 012345
> 001234
> 123456
> 
> To create a 6 figure number with the BO dropped off?
0
tjtjjtjt (488)
12/3/2004 12:35:04 PM
Thanks, you have saved me a load of time.

"tjtjjtjt" wrote:

> One way (if you don't need the letters at all anymore):
> Make ure there are  blank cells to the right of thses cells.
> Select the cells
> Go to Data | Text to Columns
> Choose Delimited and click Next
> Set B as the Delimiter
> Click Finish
> You'll end up with the Numbers in their original cell, the B will be gone 
> and the O will be in a cell to the right. You can delete the extraneous O's.
> 
> Then, with your number cells Selected:
> Format | Cells
> On the Number Tab Choose Custom
> Type 6 consecutive zeroes (000000) in the line provided. Don't use the 
> parentheses. This will give you the number format you typed out below.
> 
> tj 
> 
> 
> "Dava Sutts" wrote:
> 
> > In Cell A1,2 ,3 ect i have the following info
> > 
> > 12345BO
> > 1234BO
> > 123456BO
> > 
> > How do i convert these in Cells B1, 2, 3 etc to :
> > 012345
> > 001234
> > 123456
> > 
> > To create a 6 figure number with the BO dropped off?
0
DavaSutts (5)
12/7/2004 9:35:07 AM
Reply:

Similar Artilces:

Sent Faxes in CRM 3 don't show in History?
I'm using CRM 3 under SBS 2003. When I send a fax using the "New activity / Fax" option within a contact record it sends successfully. . However there is no record of it stored in the contact history? If I check the fax console on the server the fax has been sent (also verified by receiving person) however it doesn't get logged in the history. I can't seem to find any record of it except on the Fax Console on the server?? Anyone else have this problem or no how to solve it? ps: As a workaround I thought I might be able to automatically route faxes sent back th...

How do I copy an Excel chart with added text boxes into Word or P.
I have created a chart in Excel and added a number of text boxes on top of the chart. I need to paste the entire chart & text boxes into Word and/or Powerpoint; however by selecting the chart and copying, the text boxes are excluded. MS help suggests entering the text boxes outside the chart then moving them into the chart and grouping the chart and text boxes; however there does not seem to be the ability to group the chart with the text boxes. If you had clicked on the chart before making the textboxes, all the items would have been grouped. But you can do it later: Hold down SHI...

Customising views in CRM 3
I altered the default active user view and the advanced find user view by adding the domain logon field. However when I then use those views the additional field does not appear. When I go back to the customisation screens for those views the additional field is not present. I executed iisreset but this had no effect. Where am I going wrong? Did you "Publish" those customizations? If you didn't, that's probably why you haven't seen the changes. Thanks, Mitch ...

sum #3
When I try to calculate the sum of special cells in a row, which are copied as link fom another worksheet, the result is zero. I tried to unlock the cells, also confirmed the format of the cells as numbers. But still the result is the same. Then I copied and pasted the values of the cells in a new worksheet but still the result is zero , that should be around 100,000 Can anyone please help me? Faran Just because the cells are formatted as numbers does not make them numbers. Copy a blank unused cell, select the range of 'numbers' and Paste / Special / Add. This should make your 'n...

Highlighting text using conditional formatting
Hi All I'm trying to highlight certain text in a list - namely all text that end with river, stream or creek. I've been trying to use conditional formatting but for some reason I can't seem to get it to go (I would have thought * wildcard would have done the trick but no such luck). Ideally you would have a different colour for rivers, creeks and streams. The list is like this Grey River Brash Creek Fred Stream Hell Stream Roger River Turd Creek and so on (about 3000 entries) Any ideas about how you can do this? Feedback appreciated. Thanks Mike Try this... Select the text ...

Powerpoint Placeholder Text Box
I have a series of template pages with multiple text boxes. I would like all the text boxes to auto fit consistently if one needs to. It works like that on a two box that is in the basic package, but when I created a 3 box and a 4 box, they each auto fit based on the text in the individual box. I've tried grouping them in slide master, and normal view. Any ideas? ...

Search/Extract Data w/in Text File
Hello again! I am searching within a text file for the last row within the file. After I locate the row I need to separate the data and place into multiple cells on my worksheet. The code I have is as follows: Dim TextPath As String Dim DelimSrchChar As String Dim METtime As String Dim DataBuffer As Variant Dim NCData As Variant Dim DelimPos As Integer '=============================================== DelimSrchChar = "," TextPath = "C:\Temp\Formatted_NC_SN1008.txt" Open TextPath For Input As #1 'open text file for SN1008 Do While Not EOF(1) ...

unable to send email #3
HELPPPPPPP ! I keep getting this message....and email is returned (unless) the email address is posted in my contacts list....How do I get rid of this ??? sorry, that domain isn't in my list of allowed rcpthosts You probably need to turn on authentication to your outgoing server. You can do so on the "Outgoing Server" tab for your mail account. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:002601c3daca$8a5f0d80$a501280a@phx.gbl, Juan Caicedo ...

Installing Compact .Net 3.5
Hello, I have WinCE 6.0 R2, and am currently installing C.Net 2.0. How do I get platform builder to include Compact .Net 3.5? I don't see any Catalog entries for 3.5, just for 2.0. Thanks! --- Jeremy On 4 Mar, 20:38, "jking" <jking...@comcast.net> wrote: > Hello, > > I have WinCE 6.0 R2, and am currently installing C.Net 2.0. How do I get > platform builder to include Compact .Net 3.5? I don't see any Catalog > entries for 3.5, just for 2.0. > > Thanks! > --- > Jeremy I'm working on WinCE 6.0 R3 and I find both ...

OWA error #3
I have select Users receiving the following error when accessing OWA: HTTP/1.0 401 Unauthorized The error only occurs on a few Users. Any ideas? Thanks in advance. Try this: http://support.microsoft.com/?id=327843 -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Jeff" <anonymous@discussions.microsoft.com> wrote in message news:043e01c42482$46e0c2e0$a001280a@phx.gbl... > I have select Users receiving the following error when > accessing OWA: > HTTP/1.0 401 Unauthorized > > The error only occurs on a few ...

3 panel brochure
Hi I'm creating a 3 panel double sided A4 landscape brochure How do I ensure the 3 panels have identical widths. I have used layout guides to create the panels, but when folded the two sides don't line up properly? Using 2007 Any pointers appreciated -- Martin ���� Are your margins the same on both sides? This can be a printer issue. When you create a landscape brochure the largest margin limitation is on the right. (See Ed's web page, http://ed.mvps.org/Static.aspx?=Publisher/horidiag ) When you turn the page over to print on the back you need to adjust the pu...

Advanced Find Gripe 3
When searching in Advanced Find for Accounts, we select the field "Hospital Name" which is our primary account and then the Condition "contains" and then the value "North Hospital". We clicked go we had only one record pull up even though there are hundreds of hospitals with North & Hospital in the hospital names. So we tried putting "North * Hospital" in to the value and we got a lot more. Are there additional wildcards or boolean operators and if so where do we find the information? ...

Text From User Forms
Hi I am creating a user form where data is entered into text boxes. Once the operator has input data into a text box I want to use a command button on the user form to save the file. One text box will have a request date entered as the value and another will have the name of the requestor as the value. I want the command button to create the filename as these two values. Could someone offer some advice please. Thanks. Matt Matt, Activeworkbook.SaveAs filename: ="C:\your path\" & Textbox1.Text & " " & Textbox2.Text HTH Henry "Matt G" &l...

Autonumber #3
thank you very muc -- NG_Leed ----------------------------------------------------------------------- NG_Leeds's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1184 View this thread: http://www.excelforum.com/showthread.php?threadid=27561 you're welcome "NG_Leeds" <NG_Leeds.1femk0@excelforum-nospam.com> wrote in message news:NG_Leeds.1femk0@excelforum-nospam.com... > > thank you very much > > > -- > NG_Leeds > ------------------------------------------------------------------------ > NG_Leeds's Profile: > ht...

lost data....lost person! #3
I'll try to keep it simple....I have Windows XP Home Edition...and The Office 2000 suite....I was useing the Excel 2000 program and forgot to save the data, however, I DID NOT close the program. The data I had put in was gone when I went back to do some more entries and save and close the workbook. When I tried undo in the edit menu it said that I Couldn't undo and only after switching from sheet to sheet did it finally show that I could do one undo but it was on a seperate sheet than the one that I had showing when the option was available and I believe that it was informat...

Text to Columns #4
I have a list of the Fortune 1000 companies in one cell. Is there a way to convert this information into columns with: Company City State Telephone 1 Wal-Mart Stores, Inc. Bentonville, AR 479-273-4000 2 Exxon Mobil Corporation Irving, TX 972-444-1000 3 General Motors Corporation Detroit, MI 313-556-5000 4 Ford Motor Company Dearborn, MI 800-392-3673 5 General Electric Company Fairfield, CT 203-373-2211 6 ChevronTexaco Corporation San Ramon, CA 925-842-1000 7 ConocoPhillips Houston, TX 281-293-1000 8 Citigroup Inc New York, NY 212-559-1000 9 International Business Machines Corporatio...

Unable to cut text when forwarding
I have a recently new problem, when I forward a message I am unable to cut and paste or in anyway alter the original message, I am using Outlook 2007 and it only started yesterday. Any ideas please ...

Cancel button's ActiveControl reflects as value of text box
Hi All, On my form I have an "on exit" code to test whether its text box is "empty" or not. When I click the "Cancel" button I still get the msg that the txt box is empty. Using Debug.print to show the Me.ActiveControl gives me the value of the preceeding txt box. What I'm trying to achieve is to ignore the "empty" test if cancel has been selected by exiting the "on exit" before the msg box sub & clearly I'm misunderstanding the usage here somewhere. Hugh I'm a bit unclear what you want to accomplish... ...

publisher insists on underlining copied text ? do i remove it
I copied text from a Word Document and when it appeares in Publisher it is always underlined and i can't seem to remove the underline. Any ideas? Angelis <Angelis@discussions.microsoft.com> was very recently heard to utter: > I copied text from a Word Document and when it appeares in Publisher > it is always underlined and i can't seem to remove the underline. Paste Special as Unformatted Text? -- Ed Bennett - MVP Microsoft Publisher All the text? Try "paste special", unformatted text. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.m...

Text between Worksheets?
I've set up a budget workbook with 12 worksheets, one for each month. The first sheet contains the list of budget items. If I change the name of a budget item on the first worksheet, is there a way (or formula) to have this text automatically change on the other 11 worksheets? This would preclude my having to make the change individually on each of the other 11 sheets. How would I do this? Thanks! Select sheet2 then SHIFT + click on sheet12. In A1 enter =sheet1!A1 Drag/copy down as far as you need. The A1 will increment as you copy down. Select sheet1. Change A1 data and...

Free Form Text Box in Report
Hi All, I am running a report that runs from a Make Table Query, and then into a cross tab query. The report is in a Switchboard and runs off of a Macro that opens the make table, updates based off of the dates entered, closes it, opens the cross tab. Closes it. Opens the report in print preview. I need others to run the report with no intervention so I entered a text box (as I have done before) into the header that reads: =[Enter Report Ending Date mm/dd/yyy] to allow for the ending date to appear in the header. However, this is not working. I am recieving the following error &q...

Outbound mail #3
Exchange 2003/Windows 2003 ENTERPRISE Patched uptodate With SP1. Once a week we are having to restart the exhcnage server because the exchange server is unable to send email to external domains (any). All incoming emails are coming thru. As soon as we reboot the exchange server the outbound email will start flowing fine. Could some body please help me resolve this issue any KB article or any kind of info will be much appreciated. Ihulk On Fri, 6 May 2005 17:12:01 -0700, "Ihulk" <Ihulk@discussions.microsoft.com> wrote: >Exchange 2003/Windows 2003 ENTERPRISE Patched ...

Outlook Can't Reopen #3
I am running XP Professional with Service Pack 2 installed. Outlook 2003 also was updated via Outlook Service Packs. Problem: I open Outlook and it works and also places a icon in task bar. I exit Outlook (not close) and Icon remains in task bar. Also I cannot reopen Outlook in anyway. I must reboot. As PC closes to reboot an error message states the phrase CiceroUlWndFrame. I have used Detect amd Repair but problem remains. Anthony ...

Changing the font of text in a check box
How do I change the font properties in a check box? I would like to have the font bold and a different color. Thanks Irie! Right click the CheckBox, choose properties. Iriemon;575152 Wrote: > How do I change the font properties in a check box? > > I would like to have the font bold and a different color. > > Thanks > > Irie! -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.the...

OE adds 'Blank' to a plain text messge
When sending a plain text message in OE the recipient receives the message but the first word of the message has been added - "Blank". Would appreciate any suggestions and help. Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion...