#### Removing Letters and Dashes

```I learned here from one of you geniuses a nifty trick of how to
display only a number when there are also letters in the cell.

I learned that if I have in cell A1 the value of =93P100C=94 and, I type
in another cell, =3DLOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND
result will be 100.

I would also love to be able to do the following:

1) If cell A1 is =93A22k55-77=94, I would like the formula in another cell
to return the value 225577 (all the numbers in cell A1)
2) If cell A1 is =93A22k55-77=94, I would like the formula in another cell
to return the value of 2255 (Specific numbers from cell A1-Example 1st
4 numbers)
3) If cell A1 is 555-55-5555, I would like the formula in another cell
to return the value of 555555555 (removing dashes from a social
security number)

If possible, I=92d appreciate if you would post a formula for each of
the above scenarios.
I very much appreciate the help that you all provide us. It is
invaluable.
```
 0
SanCarlosCyclist
1/9/2010 10:16:43 PM
excel 39879 articles. 2 followers.

5 Replies
918 Views

Similar Articles

[PageSpeed] 24

```You probably did not give enough examples of your data, but if it is this
single format of A22k55-77, then try this in C1

=MID(A1,2,2)&MID(A1,5,2)&RIGHT(A1,2)

Returns 225577

then use =LEFT(C1,4) in D1

Returns 2255

For the 555-55-5555 select and under Edit > Find > - (a dash) > Replace >
(leave blank) > OK

HTH
Regards,
Howard

"SanCarlosCyclist" <sancarloscyclist@gmail.com> wrote in message
I learned here from one of you geniuses a nifty trick of how to
display only a number when there are also letters in the cell.

I learned that if I have in cell A1 the value of �P100C� and, I type
in another cell, =LOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND
({0,1,2,3,4,5,6,7,8,9},A1&"01�23456789"))),LEN(A1)),ROW(\$1:\$99))), the
result will be 100.

I would also love to be able to do the following:

1) If cell A1 is �A22k55-77�, I would like the formula in another cell
to return the value 225577 (all the numbers in cell A1)
2) If cell A1 is �A22k55-77�, I would like the formula in another cell
to return the value of 2255 (Specific numbers from cell A1-Example 1st
4 numbers)
3) If cell A1 is 555-55-5555, I would like the formula in another cell
to return the value of 555555555 (removing dashes from a social
security number)

If possible, I�d appreciate if you would post a formula for each of
the above scenarios.
I very much appreciate the help that you all provide us. It is
invaluable.

```
 0
L
1/10/2010 12:10:43 AM
```On Jan 9, 4:10=A0pm, "L. Howard Kittle" <lhkit...@comcast.net> wrote:
> You probably did not give enough examples of your data, but if it is this
> single format of A22k55-77, then try this in C1
>
> =3DMID(A1,2,2)&MID(A1,5,2)&RIGHT(A1,2)
>
> Returns 225577
>
> then use =3DLEFT(C1,4) in D1
>
> Returns 2255
>
> For the 555-55-5555 select and under Edit > Find > - (a dash) > Replace >
> (leave blank) > OK
>
> HTH
> Regards,
> Howard
>
> "SanCarlosCyclist" <sancarloscycl...@gmail.com> wrote in message
>
> I learned here from one of you geniuses a nifty trick of how to
> display only a number when there are also letters in the cell.
>
> I learned that if I have in cell A1 the value of =93P100C=94 and, I type
> in another cell, =3DLOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND
> result will be 100.
>
> I would also love to be able to do the following:
>
> 1) If cell A1 is =93A22k55-77=94, I would like the formula in another cel=
l
> to return the value 225577 (all the numbers in cell A1)
> 2) If cell A1 is =93A22k55-77=94, I would like the formula in another cel=
l
> to return the value of 2255 (Specific numbers from cell A1-Example 1st
> 4 numbers)
> 3) If cell A1 is 555-55-5555, I would like the formula in another cell
> to return the value of 555555555 (removing dashes from a social
> security number)
>
> If possible, I=92d appreciate if you would post a formula for each of
> the above scenarios.
> I very much appreciate the help that you all provide us. It is
> invaluable.

Howard, thanks for your response. I would use your suggestion if the
letter-number-dash was always the same. I am looking for a formula
that will work no matter how many numbers or letters I have in a given
cell, the results will always report on the 3 scenarios above. When
they are constant, the solution is easy by using Left, Mid, or Right
in the formula. I often have inconsistent data.
```
 0
SanCarlosCyclist
1/10/2010 12:57:29 AM
```Thought that may be the case, I am at a loss for a solution given the broad
scope of the data you mention.

Regards,
Howard

"SanCarlosCyclist" <sancarloscyclist@gmail.com> wrote in message
On Jan 9, 4:10 pm, "L. Howard Kittle" <lhkit...@comcast.net> wrote:
> You probably did not give enough examples of your data, but if it is this
> single format of A22k55-77, then try this in C1
>
> =MID(A1,2,2)&MID(A1,5,2)&RIGHT(A1,2)
>
> Returns 225577
>
> then use =LEFT(C1,4) in D1
>
> Returns 2255
>
> For the 555-55-5555 select and under Edit > Find > - (a dash) > Replace >
> (leave blank) > OK
>
> HTH
> Regards,
> Howard
>
> "SanCarlosCyclist" <sancarloscycl...@gmail.com> wrote in message
>
> I learned here from one of you geniuses a nifty trick of how to
> display only a number when there are also letters in the cell.
>
> I learned that if I have in cell A1 the value of �P100C� and, I type
> in another cell, =LOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND
> ({0,1,2,3,4,5,6,7,8,9},A1&"01�23456789"))),LEN(A1)),ROW(\$1:\$99))), the
> result will be 100.
>
> I would also love to be able to do the following:
>
> 1) If cell A1 is �A22k55-77�, I would like the formula in another cell
> to return the value 225577 (all the numbers in cell A1)
> 2) If cell A1 is �A22k55-77�, I would like the formula in another cell
> to return the value of 2255 (Specific numbers from cell A1-Example 1st
> 4 numbers)
> 3) If cell A1 is 555-55-5555, I would like the formula in another cell
> to return the value of 555555555 (removing dashes from a social
> security number)
>
> If possible, I�d appreciate if you would post a formula for each of
> the above scenarios.
> I very much appreciate the help that you all provide us. It is
> invaluable.

Howard, thanks for your response. I would use your suggestion if the
letter-number-dash was always the same. I am looking for a formula
that will work no matter how many numbers or letters I have in a given
cell, the results will always report on the 3 scenarios above. When
they are constant, the solution is easy by using Left, Mid, or Right
in the formula. I often have inconsistent data.

```
 0
L
1/10/2010 1:10:02 AM
```On Sat, 9 Jan 2010 14:16:43 -0800 (PST), SanCarlosCyclist
<sancarloscyclist@gmail.com> wrote:

>I learned here from one of you geniuses a nifty trick of how to
>display only a number when there are also letters in the cell.
>
>I learned that if I have in cell A1 the value of �P100C� and, I type
>in another cell, =LOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND
>({0,1,2,3,4,5,6,7,8,9},A1&"01�23456789"))),LEN(A1)),ROW(\$1:\$99))), the
>result will be 100.
>
>I would also love to be able to do the following:
>
>1) If cell A1 is �A22k55-77�, I would like the formula in another cell
>to return the value 225577 (all the numbers in cell A1)
>2) If cell A1 is �A22k55-77�, I would like the formula in another cell
>to return the value of 2255 (Specific numbers from cell A1-Example 1st
>4 numbers)
>3) If cell A1 is 555-55-5555, I would like the formula in another cell
>to return the value of 555555555 (removing dashes from a social
>security number)
>
>If possible, I�d appreciate if you would post a formula for each of
>the above scenarios.
>I very much appreciate the help that you all provide us. It is
>invaluable.

Easy to do with a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=NumsOnly(A1)  (for all the digits)

or

=NumsOnly(A1,n) (for the first "n" digits)

in some cell.

The function returns the digits as a string in order to preserve any leading
zero's, and also to return a blank if there are no digits in the string.

If this is not required, you may want to make some modifications.

===============================
Option Explicit
Function NumsOnly(s As String, Optional DigitCount As Variant) As String
Dim re As Object
Dim sNums As String
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
sNums = re.Replace(s, "")
If IsMissing(DigitCount) Then
NumsOnly = sNums
Else
NumsOnly = Left(sNums, DigitCount)
End If
End Function
===============================
--ron
```
 0
Ron
1/10/2010 3:28:52 AM
```Here are your answers (make sure you read the part about using
Ctrl+Alt+Enter to commit the first two formulas, not just Enter by
itself)...

[1] Posted previously by Lars-�ke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW(\$1:\$300)-1)*ISNUMBER(-MID("01"&A1,ROW(\$1:\$300),1)),ROW(\$1:\$300))+1,1),10^(300-ROW(\$1:\$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases
correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as output
(rather than 0).

[2] All the same conditions/limitations from #1 above apply...

=MID(SUMPRODUCT(--MID("01"&LEFT(A1,FIND("-",A1&"-")),SMALL((ROW(\$1:\$300)-1)*ISNUMBER(-MID("01"&LEFT(A1,FIND("-",A1&"-")),ROW(\$1:\$300),1)),ROW(\$1:\$300))+1,1),10^(300-ROW(\$1:\$300))),2,300)

[3] =SUBSTITUTE(A1,"-","")

--
Rick (MVP - Excel)

"SanCarlosCyclist" <sancarloscyclist@gmail.com> wrote in message
I learned here from one of you geniuses a nifty trick of how to
display only a number when there are also letters in the cell.

I learned that if I have in cell A1 the value of �P100C� and, I type
in another cell, =LOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND
({0,1,2,3,4,5,6,7,8,9},A1&"01�23456789"))),LEN(A1)),ROW(\$1:\$99))), the
result will be 100.

I would also love to be able to do the following:

1) If cell A1 is �A22k55-77�, I would like the formula in another cell
to return the value 225577 (all the numbers in cell A1)
2) If cell A1 is �A22k55-77�, I would like the formula in another cell
to return the value of 2255 (Specific numbers from cell A1-Example 1st
4 numbers)
3) If cell A1 is 555-55-5555, I would like the formula in another cell
to return the value of 555555555 (removing dashes from a social
security number)

If possible, I�d appreciate if you would post a formula for each of
the above scenarios.
I very much appreciate the help that you all provide us. It is
invaluable.

```
 0
Rick
1/10/2010 8:27:52 AM

Similar Artilces:

Remove a shortcut from desktop using VBA code
I have a program that uses VBA code to creates a shortcut on the desktop. I want another program to remove that shortcut. Can someone show me how to remove a shortcut using VBA code. Thank you very much. -- Larry Di sFile as String sFile = "C:\Documents and Settings\<username>\Desktop\myShortcut.lnk" Kill sFile obviously change the path and shortcut name to suit Regards, Peter T "Larry" <dietbook@msn.com> wrote in message news:67B17693-9943-460F-B069-345A3FD4D4B9@microsoft.com... >I have a program that uses VBA code to creates a...

how to write a letter of mercy to a judge
I want to write a letter of mercy to a judge but don't know how to start,can some one help me? http://www.lmgtfy.com/?q=how+to+address+a+judge+in+a+letter -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "rena" <rena@discussions.microsoft.com> wrote in message news:7547288D-0454-4E51-AB08-9D81A4273B4E@microsoft.com... >I want to write a letter of mercy to a judge but don't know how to >start,can > some one help me? > ...

RESPONSE A LETTER FROM FALSE ACCUSACION
please can you send me a sample letter response an accusacion from a customer making complain about I tried take avdance on sexual molestacion, jeez dood...I don't think you're gonna find a template for this one!...I think you're on your own...or better yet; keep your mouth shut and get a lawyer. "ed" <ed@discussions.microsoft.com> wrote in message news:825E530B-07AE-4515-B959-3FCD7BF05FF9@microsoft.com... | please can you send me a sample letter response an accusacion from a customer | making complain about I tried take avdance on sexual molestacion, &g...

Removing Blank Rows
Hello All Having trouble finding a way to remove blank row on a spread sheet this is what i need i hav a sheet like this 546 6574 7484 748589 4845845 9494 484894 93483 3483894 4384 34873 Well i need to find a way to remove the empty rows, seem like it should be easy but can't seem to get it Thanks for any help Scott One simple way is to sort and delete wholesale or use a macro (search archives for this OFTEN ask...

Removing older money version
My money 2005 installed itself in a new directory and has allowed the older version (04) to run as well. This has never happened before. How can I safely remove the 04 version without losing any important files (I archive annually and have yearly files going back to 1988)? In microsoft.public.money, Eyeman wrote: >My money 2005 installed itself in a new directory and has allowed the older >version (04) to run as well. This has never happened before. How can I >safely remove the 04 version without losing any important files (I archive >annually and have yearly files going ...

How do I remove the guideline marks I see in print preview
I am trying to print a 5x7 custom post card. During the print preview I see some print guideline marks on the borders. these print when printing the project. How do I remove these guideline marks? In the print dialog, clear crop marks, click the advanced print options, page settings tab... (2002 & 03). In other versions it is about the same. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "trasmus" <trasmus@discussions.microsoft.com> wrote in message news:4806177C-D775-4692-B52A-14E28E4A0368@microsoft.com... &g...

Removing side tabs on forms
Is it possible to remove the side tabs in specific record types (eg. contacts, accounts, ...)? For example: I would like to remove SALES side tab on Contacts form, but it should not be eliminated in Accounts form. Thanks, m you cant eliminate tabs in one area without affecting the rest. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Mikkhail" <Mikkhail@discussions.microsoft.com> wrote in message news:545B1D48-E1BC-4C3C-AF8D-A231E27F3AC1@microsoft.com... > Is it possible to remove the side tabs in specific record types (eg. > contacts, accounts, ....

dashed fill
Hi, I'd like to use a hashed/horizontal line fill for one of the series in my chart, but it doesn't seem that this simple option is available on Excel. Please help me with this. Hi, Patterns were deprecated in xl2007, at least from the UI. This addin allows you to apply pattern fills. http://www.andypope.info/charts/patternfills.htm For xl2003 double click series to display Format dialog. On Patterns click the Fill Effects button. On the Pattern tab you can choose required fill pattern. Cheers Andy fill for series wrote: > Hi, I'd like to use a hashed/horizontal line fi...

Publisher News letters for e-mail distribution
I believe many of us Publisher users who create newsletters convert them to a pdf file for distribution. Recently, I changed out my Acrobat Reader 6.03 to 7.01. In doing so, whenever I use an apostrophe s ('s) with "smart quotes" on the word/s are displayed in Acrobat Reader with a space between the last letter of the word and the apostrophe s. The apostrophe is into, above the area of the s. Anyone else experiencing this oddity? The error shows up also with quotes when "smart quotes" is on. I've tested this with Publisher 2000, Word 2003 and Lotus Word P...

After trying to remove this distasterous program, the add-in still tries to load when I start Outlook XP. I have done a detect and repair to no avail. The exact error message: "The add-in "c:\program~1\ringcen~1\ringce~1\r0faxext.dll" could not be installed or loaded. This problem may be resolved by using detect and repair on the help menu. Unable to load" c:\program~1\ringcen~1\ringce~1\r0faxext.d..." You may be out of memory , out of system resource or missing a .dll file. " Hell yeah I am missing a DLL file...I uninstalled the damn program!! Any suggest...

I need to replace/remove quotes from text...
I'm trying to use this in a query, but I can't figure out how to remove the quotes around text ie "Nashville" need to be Nashville. Company_Name: Replace([CompanyName]," ","") I'm using Access 2003 Thanks Kelvin Replace( CompanyName, """", "" ) Hoping it may help, Vanderghast, Access MVP "Kelvin" <someone@domain.com> wrote in message news:uNxdTTGLIHA.2176@TK2MSFTNGP06.phx.gbl... > I'm trying to use this in a query, but I can't figure out how to remove > the quotes around text ie ...

remove dublicate rows
I have a spread sheet that has about 1500 rows. several of these rows are duplicates. The columns go from A to G I already sorted everythign by column A I would like a macro that can go through and find out the rows that are a duplicate or the row above it. For it to be a valid dublicate, it has to be a dublilcate of all the columns too Any ideas? Doing this manually bites. How about using a helper column with this formula (starting in H2): =SUMPRODUCT(--((A1:G1)=(A2:G2))) Then drag down. Now apply Data|filter|autofilter on that column. Show only those rows that equal 7 (a:g). del...

Hi there... How do I remove two email addresses that appear when I send mail eg; Someone [someone@someone.com] <someone@someone.com> Hope that makes sense :) TIA ...

Columns have changed from letters to numbers
There's got to be a quick and easy answer to this problem. Somehow the column designations on our Financial Secretary's computer have all changed to numbers instead of letters. It was probably an accidental few keystrokes or mouse clicks that did it. It makes it quite confusing to read cell designations and makes formulas all but impossible to edit . What do I do to change them back? Thanks. Morris Driggers, Minister of Music and staff tech advisor First Baptist Church, Clinton, SC tools>options>general>r1c1 -- Don Guillett SalesAid Software dguillett1@austin....

Instr question- leaving off a letter
Hello- I have this statment in my VB behind my form. Me.ReducerBushing = Right(Me.ReducerSheave, InStr(Me.ReducerSheave, " "))) My textbox "ReducerSheave" would be something like : 2 3V 690 SDS. I am trying to isolate the "SDS". Its not always 3 characters, in fact normally its 2. When it is 2 characters (2 3V 550 SH) It works fine. I don't understand why it matters when my instr is supposed to give me everything after the first space from the right. I obvoiusly don't understand exactly what my instr() asks for specifically. Can someo...

Removing a recurrence
I inherited a ms outlook version through my employer and have a recurring appointment that I can't seem to remove. I have tried several ways and have gone through help but nothing works. How can I remove this recurring appointment from the old user? I am using Outlook 2000 SR-1. what do you mean by remove it from the old user? -- I hope this is what you needed. Judy Gleeson MVP Outlook Outlook trainer and author of Productiv_IT with Outlook www.acorntraining.com.au "I not only use all the brains I have, but all I can borrow." (Woodrow Wilson) "bill" <plops...

I have an overdue bill notice stuck on my home page under "Bills and Deposits" yet under the regular bills abd deposits I don't see that bill. What happened was this: I had a bill that was for \$150. I setup a recurring bill payment for \$25 a month and set the payment number to "6" until it would be paid off. When there was \$50 left, I just paid the whole bill. The next month rolled around and somehow money05 thought that bill still needed to be paid and now shows it as past due. I have closed the account. I have tried deleting the account altogether. I have t...

how do I program to add/remove/modify rule of outlook & outlook express
I have read groups but didn't find how to programmly modify rules of outlook or outlook express.I have found that there is a way to modify rules in the exchange server, but what I want is to modify rules in the client. any one have idea? tks. You would need to upgrade to Outlook 2007, which is the first version = with rules programmability.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://w...

sendkeys sends every other letter Excel 2007 to Outlook 2007
I am running a macro that uses Sendkeys to send text from an Excel cell to the body of an email in Outlook. Worked fine in Office 2003 but since upgrade to 2007 it now only sends every other letter. So, for example, if the value in the cell was "abcdefghij", then Outlook would only receive "acegi". Any ideas? You didn't post any code so it's not possible to answer your question. That said, SendKeys is notoriously unreliable; hard to be sure without knowing what you are doing but most likely you can accomplish your objective by automating Outlook. ...

OE removes attachments
Could anyone help me in setting up Outlook to make sure that it doen not remove certain types of files automatically. eg PDF, HTML. Thanks "sam" <anonymous@discussions.microsoft.com> wrote in message news:3c9a01c42a8c\$f19cf540\$a401280a@phx.gbl... > Could anyone help me in setting up Outlook to make sure > that it doen not remove certain types of files > automatically. eg PDF, HTML. > > Thanks Tools-Options-Security This newsgroup is for support of Outlook 97/98/2000/2002/2003 from the Office suite of products. Outlook Express is actually a separate progra...

Is there any way to remove an e-mail hyperlink in OE6?
I'm trying to make an e-mail address plain text. Underline is NOT checked. I have selected the text, checked and unchecked it a dozen times. I've clicked on Format>Plain Text and back to Rich Text. Then back to Plain Text. Then back to Rich Text. Then changed the font, then changed it back. Then selected the text and unchecked underline. Then underlined again and deselected underlining. Well, you get the idea. I was able to change the font from blue to black, but still can't remove the underline no matter what I do. Any ideas? No way to do that without munging ...

Find and remove rows with the word "TRUE" in Excel
Hi, Here is a bit of code that will delete all the rows that do not have an entry in col "B". intRow = 2 Do Until ws2.Cells(intRow, 1).Value = "" If ws2.Cells(intRow, 2).Value = "" Then 'Delete row range ws2.Rows(intRow & ":" & Row).Delete 'ROW is the last used row defined earlier. intRow = intRow + 1 Else intRow = intRow + 1 End If Loop My next task is to delete all the rows with the word "TRUE" in col "B". If I put: If ws2.Cells(intRow, 2).Value ...

removing fonts from dropdown list in Word 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When you click on Fonts in Word, you get a very long list of fonts, most of which I never use and would therefore like to remove. <br><br>How do I do that in Word 2008? I could do that in Word 2004! <br><br>I have already disabled them in Font Book. <br><br>Thank you. If you "never use" them, why have them on your system? Keeping fonts loaded that you do not intend to use simply wastes memory and slows your whole system down. If you remove the fonts from the syste...

Publisher 2002: How to remove empty pages left by deletion of text
My wife deleted about six pages of text from a long document (300+ pages). However, the deletion left 6 empty pages (that is, the gap didn't close up). She has tried various kinds of dlete and cut editing commands, but nothing so far has worked. EmoryW wrote: > My wife deleted about six pages of text from a long > document (300+ pages). However, the deletion left 6 empty > pages (that is, the gap didn't close up). She has tried > various kinds of dlete and cut editing commands, but > nothing so far has worked. ============================= I guess you tried...Edit / ...

How do I remove a symbol on every line of my document?
I can't copy and paste the symbol. It is on everyline. It looks something like this II See http://word.mvps.org/FAQs/Formatting/NonPrintChars.htm. But if it is on every line, then either you're pressing Enter at the end of every line or you've pasted some plain text; in the latter case, see http://sbarnhill.mvps.org/WordFAQs/CleanWebText.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "summer" <summer@discussions.microsoft.com> wrote in message news:D9969843-4376-4AA5-B969-E73A6ED1E4...