How will I do Excel user-defined function to extract letters from string

How will I extract letters from this string
like...         to:

AB-123456-45    AB
BCD-678901-23   BCD
E-23454         E

if I this bottom code extract numbers from a the same string:

Function ExtractNum(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String

sText = rCell

      For iCount = Len(sText) To 1 Step -1
            If IsNumeric(Mid(sText, iCount, 1)) Then
                  i = i + 1
                  lNum = Mid(sText, iCount, 1) & lNum
            End If

            If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
      Next iCount

ExtractNum = CLng(lNum)
End Function


0
Wavit11 (1)
4/30/2004 1:50:50 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
310 Views

Similar Articles

[PageSpeed] 23

Perhaps this would suffice:

Assuming your sample data is in col A, A1, down

AB-123456-45
BCD-678901-23
E-23454
etc

and the data structure remains similar to the sample

Try in B1: =LEFT(TRIM(A1),SEARCH("-",TRIM(A1))-1)

Copy B1 down

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

"Wavit11" <Wavit11@mitsuiimi.com> wrote:

>How will I extract letters from this string
>like...         to:
>
>AB-123456-45    AB
>BCD-678901-23   BCD
>E-23454         E
>
>if I this bottom code extract numbers from a the same 
string:
>
>Function ExtractNum(rCell As Range)
>Dim iCount As Integer, i As Integer
>Dim sText As String
>Dim lNum As String
>
>sText = rCell
>
>      For iCount = Len(sText) To 1 Step -1
>            If IsNumeric(Mid(sText, iCount, 1)) Then
>                  i = i + 1
>                  lNum = Mid(sText, iCount, 1) & lNum
>            End If
>
>            If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
>      Next iCount
>
>ExtractNum = CLng(lNum)
>End Function
0
demechanik (4694)
4/30/2004 2:16:21 AM
Reply:

Similar Artilces:

Formatting Excel
I have a table in excel, all of the information in this table is linked to AutoCAD. I have added an example below: Example 1 NO. NAME BOTTOM BOTTOM TOP TOP MAT FIN MAT FIN 101 CORRIDOR PLASTIC SMOOTH RUBBER SMOOTH 102 CORRIDOR PLASTIC SMOOTH RUBBER SMOOTH I am trying to get Example 1 to look like Example 2 without having to re-enter everything. It is the same info. but a different layout. I tried a Pivot Table but was not successful. Does anyone have any ideas? Example 2 NO. NAME BOTTOM TOP 10...

Error opening Excel File
Hi, I'm currently having these days an error while opening an excel sheet. The sheet is shared among five users. When one tries to open it, a message indicates that the file is locked for editing by another user and after checking with the user, I understand that he has already closed the file but the latter remains locked. Can someone help? ...

Visibility of new users in Contacts
I have done this many times with no problems. Now I have one user and one user only who does not show up in the list of users in Outlook. I made sure it is not hidden. Any idea why this one user is not visible? It is the third 'Mark' in my list of users, not that it should matter. Any idea's would be greatly appreciated. -- Keith Baca kbaca@baytekgames.com On Wed, 19 Apr 2006 06:21:02 -0700, Keith Baca <KeithBaca@discussions.microsoft.com> wrote: >I have done this many times with no problems. Now I have one user and one >user only who does not show up in t...

mail will not open
when trying to open some...not all...emails I get the message "there was an error opening this message.....an error has occurred". This doesn't happen to all messages nor does it happen to all messages from the same sender. "velona" <velona@discussions.microsoft.com> wrote in message news:5CFA4037-506D-4362-B110-13A9915D825A@microsoft.com... > when trying to open some...not all...emails I get the message "there was > an > error opening this message.....an error has occurred". > This doesn't happen to all messages nor doe...

show month number as month name in Excel?
I'm trying to format a field in an Excel Spreadsheet in such a way that it displays a month name rather than a month number. For Example, if a cell contains 8, I want the cell to show as August. Conditional formatting only allows me 3 levels of condtions and isn't therefore suitable. I don't think you're gonna be able to use formatting for this. But you could use a helper cell: =date(2005,a1,1) if A1 contained the month number. Format this cell as MMMM and you can keep the value as a real date. or =text(date(2005,a1,1),"MMMM") If you want the text string fo...

signing VBA Macros for Excel
Hi, Below is a question from one of my friend. would be great if someone can answer this "We have a requirement to sign VBA macros in Excel sheet, will only self-signed certificates work or any certificate installed in root certificates folder can be used, if yes how to install the server certificate to use in Excel VBA" -- Thanks, C.Prashanth C.Prashanth - http://vbadud.blogspot.com/2008/04/creating-digital-signature-for-vba.html - Mike http://www.MikeMiddleton.com "C.Prashanth [MSFT]" <prashcha@online.microsoft.com> wrote in me...

Excel 2007 will not refresh cells
I am currently using Excel 2007. I have a spreadsheet that I want to use to do some analysis of various situations. So here is my problem. I have several columns that have totals at the bottom. I have no problem getting the columns to total initially. However, if I plug in another number in the column, the total stays the same and won't refresh or re-total the column. I don't recall that this was a problem when I worked with Excel 2003. Can someone please tell me how to get the totals to update when I enter some new numbers in the column? Thanks in advance for the...

subform will not refresh
I have a subform for jobs to be at list - when I am int the actual form it requeries and shows only the ones checked off - when I go to the mainform with the jobs to be at list as a subform - it does not refresh or requery unless I close and open back up again. I had added a refresh command button with the following code: DoCmd.RunCommand acCmdRefresh when i press the refresh button it refreshes the job list to be at I add this code to the Onload of the Main form - don't want user to have to press refresh button but it doesn't refresh jobs to be at list unless press ...

Excel 2002 Color Printing
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C59124.DB3F0E00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Printing in black and white only when the workbook has colored = background and colored letters and numbers. The "Print in black and = white is not checked". ------=_NextPart_000_0006_01C59124.DB3F0E00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML>...

Need help with formulas and functions
I am building a testing chart for equipment. I have 5 tests on each item and have to put in their numerical value. I need to average the values dropping the highest and lowest values as sometimes the values can be a misread of the test equipment. The question is, is ther are formula, argument or function that can be as follows: =AVERAGE (C1:F1) ignore =MIN(C1:F1) and =MAX(C1:F1) I have been looking through the help built in to Excel and have not had success in finding the answer. greyhwk Greyhwk, If your data were in A1:A5 =(SUM(A1:A5)-MIN(A1:A5)-MAX(A1:A5))/(COUNT(A1:A5)-2) Dan E &...

messages will not display on Outlook Express
I cannot get my messages to display the text of the message. I can send and receive, but when I actually try to open a message the message will pop up, but without text. The hourglass icon is displaying, seeming like it's trying to pull the message up but it never comes. Does anyone know what I should do? anonymous@discussions.microsoft.com <anonymous@discussions.microsoft.com> wrote: > I cannot get my messages to display the text of the > message. I can send and receive, but when I actually try > to open a message the message will pop up, but without > text. ...

How do I limit the visible rows in Excel?
I want to hide everything in a worksheet except for the table into which I am asking others for input. Columns are easy, but how can I hide ALL the rows below my chart? I could colour them out in black, that would lead to printing issues. Help please! Tom, One way I like to restrict access is by defining a scroll area thus: Sub DefineScrollArea() ActiveSheet.ScrollArea = "MyCellRange" End Sub Once you run this macro, you can only move the cursor to a cell within this range. You can also use cell addressses instead of the "MyCellrange" range name Sub DefineSc...

Excel Worksheets collection
Greetings, all: I am in the midst of teaching some C# and I do not have Excel in front of me, but a student posed a question about how to refer to a given worksheet inside a workbook. Can I say something like this? exlApp.Workbooks(0).Worksheets(0)... to refer to the first worksheet of the first workbook? Can someone verify the syntax? Are these collections0-based or 1-based? -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "Escriba coda ergo sum." -- vbSensei Both collections are 1 based. The workbook indexing apparently depends on the order...

You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com
You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com ...

Is Excel causing me system problems?
I have a problem when I open certain spreadsheets that are on a server. My laptop slows to a crawl. I don't think it's an Excel problem, I think it may be more of an Excel configuration problem. I can't find anything in Excel that isn't right. It takes me 20 minutes just to open the file and another user can open it in 2 minutes. What might I look at in the system tools etc that could tell me specifically where my problem is? As in, do I need more memory or something like that. Thanks "Sandra" <ab@mdahospital.com> wrote in message news:%23Lk99e6OFHA.688@TK...

Disabled users in Exchange 2003/2000
Hello; Can anyone tell me what the default behavior is for disabled users in Exchange 2000 and 2003 . I alsways assumed all mail would bounce when the AD account is disabled. Looking at article it would seem that the desired behavior is to have the mailbox accept messages. A test account worked as intended, the message was bounced when the account is disabled. I am working with an Organization that has several hundred disabled accounts and they just found out that most of them are receiving mail. They want them to stop receiving mail, but have the mailboxes still accessible when needed. ...

Please help an Excel Newbie!
I am trying to use Excel to make a simple time tracker. This is th basic set up of my page: Cells A4-R4 contain # of hours worked per day, say 5:30 (Time) in eac cell. Cell A2 is a running total of A4-R4. This formula I have already done =SUM(A4:R4) Cell A3 is the % of a total # of hours available that A2 is. Fo example; A2 is 40 and the total available # of hours is 100 (which i A1) then A3 would be 40%. What would be the formula used to show the percentage of A1 that A2 i if A1 is a number such as 100 and A2 is in time format? When I forma A2 to time my % gets screwed up each time I t...

with Publisher or Office.Will I be able to access old files docume
I am frustrated with my Open Office org. program for many reasons. I would like to use Publisher or Office or? Will I be able to access, email, documents etc files previously made on another program if I download Publisher or Office or ? Erica wrote: > I am frustrated with my Open Office org. program for many reasons. > I would like to use Publisher or Office or? > Will I be able to access, email, documents etc files previously made on > another program if I download Publisher or Office or ? Office applications cannot open OpenDocument files. You will have to convert them to O...

Excel is creating temp files Help!!!
Hi i have to files in excel, i cant figure it out, whenever i open th files, they create temp files into the same location, when i shut dow the program the temp files are left there. Is their a way to make it so temp files are not saved. Or is their a way to make it so that the creation of temp files i turned off. Thanks jaso -- greenfalco ----------------------------------------------------------------------- greenfalcon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1362 View this thread: http://www.excelforum.com/showthread.php?threadid=26182 What do th...

How do I expand the number of characters allowed in an Excel cell.
The default for maximum number of characters in a single Excel cell is 1024. If you enter more than that all you see is astricks (********). If you select the cell where the astericks show, you can read the information in the formula bar above. However, it does not print out well. I am importing web data from a Web Based Application into Excel and require a column for comments which normally falls below the default maximum. On those occassions it does not, it is a pain. I have run into this problem for quite a while and have finally decided to put it out there for the more advan...

My Money Side will not stop
I read the FAQ for Money 2002 and it stated A) Click Help in MoneySide, then Settings. Under "Open MoneySide" select "Never" and then "Done." I select "Never" but there is no "Done" I can find and My Money leaves for this time only to return again. Is there any way to kill money from running in the background. It is not running in MSCONFIG on my XP sp2 system that I can find. Thanks Don I wish I had a way to test this here to tell you how to do it and validate the FAQ. Hopefully somebody who can still test on M02 will join the thread....

Excel and Pictures
I would like to understand how Microsoft Excel handles pictures when it is deleted. I have come to realize that the pictures that are inserted into Microsoft Excel and then deleted, are not really deleted but hidden between lines between the rows. I have tried the activesheet.shapes.selectall function to try remove it but it still is not perfect. I deal generally with a lot of pictures in Excel, and would really like to understand how Excel handles pictures specifically. Thank you for your time. Jin How did you reach the conclusion that pictures deleted by the user are hidden by XL...

Excel
Amidst a long listing of multiple values, X1 = 2.0, Y1 = 7.00 ; X3 = 2.1, Y3 = 7.51 . For X2 = 2.05, Y2 = ? Please devise a worksheet formula in order to interpolate the values thereof. Regards. One way using FORECAST() If the known x and y values are in cols A and B, row2 down, viz.: Values of X1 and X3 are in A2:A3 Values of Y1 and Y3 are in B2:B3 X2's value of 2.05 is in A4 Try in B4: =FORECAST(A4,B2:B3,A2:A3) which returns 7.255 -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com --- "TKT-Tang" <tkt_tang@hotmail.com> wro...

can a workbook with macros created in excel 2003 work in excel 20.
I created a workbook in excel 2003. I then made changes to it on another computer using excel 2000 and saved it to a CD. When I open the file up in excel 2003 I get a message that says "data may be lost". When it does open all the macros are lost. Are ther limitations working between excel 2000 and 2003? I suspect that your problem is not the version difference but is due to the fact that you saved it to a cd. Never save a file to a cd or floppy. Always save the file on the HD and then COPY to the cd. Then, copy from the cd to the HD and then open from the HD. However, there ma...

Reverse-engineering an Excel pivot table-type dataset
I have a dataset in pivot/crosstab format. Can anybody think of an easy way to take this dataset and turn it into a database-ready table? That is to say, data is in the format: Name Jan Feb Mar **** **** **** **** John 2 4 6 Mary 3 1 8 and I want to wind up with Name Month Qty **** ***** *** John Jan 2 John Feb 4 John Mar 6 Mary Jan 3 Mary Feb 1 Mary Mar 8 T...