+ Reply to Thread
Results 1 to 11 of 11

Remove certain characters from a cell

  1. #1
    Registered User
    Join Date
    06-15-2013
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    7

    Remove certain characters from a cell

    Hi, I need to remove certain characters from a cell and have no clue. I've tried to use REPLACE and SUBSTITUTE but none of them made exactly what I need. Let me explain.

    I have imported prices from CSV and they are formatted as text an they contain space, EURO symbol and comma (like 13 920,00 ). I can't change this. The problem is, that the when I use SUBSTITUTE, I can remove the last 5 characters and the result is fine. The problem is when there are some cents (like 6 258,30 ) - than the result is the same as original text.

    I've tried also REPLACE function to remove exact number of character but here I had problems with different amounts (removing everything from 5 character works for 13 920,00 but not for 46,20 ). Is it possible to remove character from the end and not from the beginning?

    Please check the enclosed file, it will be more clear what I mean.

    Thanks for any help!

    sample.xls

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,297

    Re: Remove certain characters from a cell

    Hi and welcome to the forum

    try this, copied down...

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","."),"€","")," ","")*1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    566

    Re: Remove certain characters from a cell

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"€",""),",",".")," ","")

    This will remove spaces, commas and euro symbol.

    =SUBSTITUTE(A2,"€","")

    this will just do the euro symbol if that all you require

    EDIT: Fdibbins has put *1 in the formula which will convert the result from text to a number. Good shout!
    Say thanks, click *

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Remove certain characters from a cell

    Hard to tell what results you're expecting from your description + the posted book.

    Perhaps

    =LEFT(A2,LEN(A2)-1)

    If that's not it

    In addition to the incorrect results you've tried, can you repost the book with a column containing the results you're trying to achieve?

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove certain characters from a cell

    Maybe try this instead:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  6. #6
    Registered User
    Join Date
    06-15-2013
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Remove certain characters from a cell

    Thank you very much FDibbins! It works perfectly... :-)

  7. #7
    Registered User
    Join Date
    06-15-2013
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Remove certain characters from a cell

    Hi abousetta, your script works also and the result for me is the same...

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove certain characters from a cell

    Thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    abousetta

  9. #9
    Forum Contributor satputenandkumar0's Avatar
    Join Date
    11-08-2012
    Location
    Pune, India
    MS-Off Ver
    Office xp & Office 2007
    Posts
    398

    Re: Remove certain characters from a cell

    Dear M8R-l3mfmi,

    try this

    Please Login or Register  to view this content.
    Regards,
    Nandkumar S.

  10. #10
    Registered User
    Join Date
    06-15-2013
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Remove certain characters from a cell

    Hi guys, ale you posts work good for the purpose I've asked. Now I received content in different format and the "numbers" contain additional characters, see below.

    Please Login or Register  to view this content.
    and second content like this:
    Please Login or Register  to view this content.
    I've tried to figure out the conversion to real working numbers with above mentioned scripts but with no success :-(

    Thanks for any help!
    Last edited by M8R-l3mfmi; 07-03-2013 at 04:23 PM.

  11. #11
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove certain characters from a cell

    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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