+ Reply to Thread
Results 1 to 2 of 2

Use TRIM function to strip part of a string, replace data in stripped cell with result. VB

  1. #1
    Registered User
    Join Date
    04-13-2006
    Location
    Toronto
    Posts
    6

    Use TRIM function to strip part of a string, replace data in stripped cell with result. VB

    Any assistance would be appreciated on my question below.
    I have a spreadsheet with two columns that already have data and a third column that I am using the TRIM function on to take part of the value in column B. Here is the function that I am using in column C ‘=VALUE(TRIM(CLEAN(RIGHT(B2,6))))’.
    As can be seen, it works great when the value in column B does not have any alpha characters.

    A B C
    7400 AAAA\002810 2810
    5481 AAAA\000210 210
    4632 AAAA\ahmede #VALUE!
    4091 AAAA\002950 2950
    3711 AAAA\000803 803


    I need to do three things:
    1) Get the formula to work for all lines
    2) Get the result from column C to replace the info in column B as a value
    3) Create a VB macro so I can run it each month.

    Thanks in advance for your assistance
    Wingale

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Use TRIM function to strip part of a string, replace data in stripped cell with result

    VALUE function converts a string that represents a number to an actual number. But 'ahmede' is not a string that represents a number so the error.
    ________________________________________________________
    If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.

    Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find a string by VBA Regular Expression and replace a part of that string
    By taps in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-25-2013, 11:08 AM
  2. [SOLVED] Function to trim first 10 characters off a string
    By theletterh in forum Excel General
    Replies: 5
    Last Post: 07-12-2012, 11:43 AM
  3. Help with Trim(string) function
    By proepert in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-10-2010, 12:33 PM
  4. Excel Function to trim cell data
    By andrew8008 in forum Excel General
    Replies: 3
    Last Post: 01-12-2009, 01:21 AM
  5. [SOLVED] Find and replace part of a text string
    By [email protected] in forum Excel General
    Replies: 2
    Last Post: 07-10-2006, 05:39 PM

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