+ Reply to Thread
Results 1 to 13 of 13

Remove All Text,Special Characters, & Spaces

  1. #1
    g48dd
    Guest

    Remove All Text,Special Characters, & Spaces

    Excel 2003: I have a column that looks that has values in it like this:
    Please Login or Register  to view this content.
    I need to reduce the column to just plain numbers no % no text, I have a program that does this but it is thrid party add-in and can't be used in a macro. I tried find and replace but that does not help as there are spaces and the spaces are not the same or in the same place. There is also for some reason special characters the / pops up in 5 different cells. I am trying to make this column so that I can sort it.

    Thank you
    Ken
    Last edited by g48dd; 05-31-2011 at 12:09 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,460

    Re: Remove All Text,Special Characters, & Spaces

    Here, try this:

    =LOOKUP(99^99, --RIGHT(SUBSTITUTE(A1, "%", ""), ROW($A$1:$A$10)))

  3. #3
    g48dd
    Guest

    Re: Remove All Text,Special Characters, & Spaces

    Thank you, Let me apply it see how it does

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Remove All Text,Special Characters, & Spaces

    assuming:
    - the data in column A
    - the numbers are being preceded by a space

    Please Login or Register  to view this content.



  5. #5
    g48dd
    Guest

    Re: Remove All Text,Special Characters, & Spaces

    Well I don't think I am using the lookup correctly, I moved the data to Column D D30:D40 and placed the lookup formula in Column A at A30 and this is how I changed it

    =LOOKUP(99^99, --RIGHT(SUBSTITUTE(D30, "%", ""), ROW($D$30:$D$40))) this is the formula at A30 and the I copied it down, but the return I get is #N/A?

    I have not tried the VBA yet I have no spaces preceding all the spaces are in between the words BLACK space| or two spaces CARTRIDGES space|sometimes two spaces 97%

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,460

    Re: Remove All Text,Special Characters, & Spaces

    Leave last part as it is and with $ around it ... Change only A1 into D30:


    =LOOKUP(99^99, --RIGHT(SUBSTITUTE(D30, "%", ""), ROW($A$1:$A$10)))

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Remove All Text,Special Characters, & Spaces

    I fear you didn't read my post correctly.
    2 or more spaces do not matter. The only thing that matters is a space before the number.

  8. #8
    g48dd
    Guest

    Re: Remove All Text,Special Characters, & Spaces

    Zbor I am totally freaked out I have no idea why that works, don't explain yet I just want to look at and see if I can figure it out.

  9. #9
    g48dd
    Guest

    Re: Remove All Text,Special Characters, & Spaces

    SNB.... I can't make it work but I think I kow what is wrong, to use as is without modifying it, does it need to be placed on Sheet 1 ?

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Remove All Text,Special Characters, & Spaces

    or
    PHP Code: 
    =LOOKUP(99^99; --100*RIGHT(D30ROW(1:10))) 

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,460

    Re: Remove All Text,Special Characters, & Spaces

    Here is explanation:

    In Excel, the Lookup function returns a value from a range (one row or one column) or from an array.

    If you put very big number it will return last value
    (for example: =LOOKUP(9, {8,2,4,3,6,5}) will return number 5)
    But if you have number 11 in your array you will get wrong result.
    So you need to put very, very big number (such as 99^99 (which is 3,69729637649726E+197 so hardly you will have bigger number than that). If it's not enough you can use 9.99999999999999E+307).

    So, =LOOKUP(9.99999999999999E+307, {8,2,4,3,6,5}) but now you are sure that there can not be any higher number then your criteria so it will always return last number from an array).

    Now, when you know this you can split your word Black Cartridge 27% from the right (lats say it's in D30 as snb showed).

    --100*RIGHT(D30; ROW(1:10)))

    ROW(1:10) will give you number 1,2,3,4,5...,9,10

    if you can have higher number then 10 digits (i.e. 14) you can put ROW(1:14) or any higher just to be sure ROW(1:100)

    Now, it will take right part of your string with: 1 character, then 2, then 3 etc... up to 10 (or 14 or 100)

    RIGHT("Black Cartridge 27%", ROW(1:10)))

    Will give you an array: "%", "7%", "27%", " 27%" (there is space), "e 27%", "ge 27%", "dge 27%", "idge 27%", "ridge 27%", "artridge 27%"

    -- or 100* (no need for both here but anyaway you need to convert those strings into numbers)

    So converting it by -- or by 100* you will have new array:

    100*"%", 100*"7%", 100*"27%", 100*" 27%", 100*"e 27%", 100*"ge 27%", etc

    Multiplying you'll get result:

    #VALUE!, 7, 27, 27, #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!

    And LOOKUP from the begging of explanation
    =LOOKUP(9.99999999999999E+307, {#VALUE!, 7, 27, 27, #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!})
    will return last value that is not an error and that's 27 which si last number in your string.

    Notice that if you change % with any other character (i.e. Black Cartridge 27# ) you will get error because right character would be text and you can never convert it to number.

    Then you need to take my approach and substitute last characters while snb elegantly solved it by multiplying with 100.

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Remove All Text,Special Characters, & Spaces

    If they are all percentages, you could also use:
    =-LOOKUP(1,-RIGHT(D30,{1,2,3,4,5,6,7,8,9}))*100
    Remember what the dormouse said
    Feed your head

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Remove All Text,Special Characters, & Spaces

    For the VBA-solution see the attachment.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1