+ Reply to Thread
Results 1 to 5 of 5

Trying to find a way to add up Hyperlinks or remove and then let me add

  1. #1
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Trying to find a way to add up Hyperlinks or remove and then let me add

    There is a file we pull from a system and it gives back the amounts in hyperlink form, however, I am trying to add those all up without having to remove or retype the hyperlinks amounts. I was wondering if anyone could help me out. Ive tried the "copy 1 paste special trick", it worked but would not let add them up and only counted. Have to do this each month, so trying to figure out an easier way to do this month-to-month.

    Column A is what I what I would want the result to look like. Just simply entered into a1 =b1 and down to give example, but at the bottom it also shows how it would not add those up either. End result, one column with just the number with no links and able to add them up because I have to sort them out by accounts.

    Thanks,
    Ant
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,861

    Re: Trying to find a way to add up Hyperlinks or remove and then let me add

    The values you have in column B are text values, and have a non-breaking space character (with code of 160) at the end. Change your formula in A1 to this:

    =SUBSTITUTE(SUBSTITUTE(B1,"$",""),CHAR(160),"")*1

    and this will convert them to proper numbers, so that Excel can SUM them correctly.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Trying to find a way to add up Hyperlinks or remove and then let me add

    Thanks, Pete that worked. But is there another way to do it to that column by itself without the formula? So I could just go through the steps or no? Also this would help in explaining to others at work. If not, I appreciate the help.

  4. #4
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Trying to find a way to add up Hyperlinks or remove and then let me add

    Or I guess, how did you know of the "char 160" problem?

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,861

    Re: Trying to find a way to add up Hyperlinks or remove and then let me add

    I suspected that those were being treated as text values, so I tried this in A1:

    =B1*1

    but that returned an error (#VALUE), so there was something else that was causing it to be a text value. At first I thought it was the $ symbol, so I tried this formula in A1:

    =SUBSTITUTE(B1,"$","")*1

    That also returned an error, so in a blank cell I put this formula:

    =LEN(B1)

    which returned 10, although there were only 9 characters that I could see. So, it looked like there was some kind of space in there, and as it wasn't visible at the beginning of the value it must have been at the end. I then put this formula in another blank cell:

    =CODE(RIGHT(B1))

    which returned 160, and confirmed that there was a non-breaking space character at the end. Thus I had to modify the original formula to get rid of both the $ and the nbsp.

    Hope this helps.

    Pete

+ 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] Remove Hyperlinks function in Hyperlinks
    By Jan Lichtenbelt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-10-2014, 07:36 AM
  2. Find Hyperlinks, Copy Hyperlinks to alternative sheet, print all hyperlinks
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 05:13 PM
  3. how to find and remove non active hyperlinks
    By rumrak in forum Excel General
    Replies: 5
    Last Post: 07-15-2009, 01:20 AM
  4. Remove Hyperlinks
    By Otto Moehrbach in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2006, 02:05 PM
  5. Remove hyperlinks
    By dminkov in forum Excel General
    Replies: 1
    Last Post: 06-09-2006, 08:50 AM
  6. [SOLVED] Remove Hyperlinks
    By dthmtlgod in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2005, 09:10 AM
  7. [SOLVED] How can I remove the hyperlinks?
    By SantistaS in forum Excel General
    Replies: 6
    Last Post: 04-29-2005, 04:07 AM

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