+ Reply to Thread
Results 1 to 3 of 3

Values stored as text

  1. #1
    Registered User
    Join Date
    07-01-2021
    Location
    Wolverhampton, England
    MS-Off Ver
    MS Office 2019
    Posts
    9

    Values stored as text

    Hi I have the follwoig spreadsheet (see attached) the values within it I believe are numbers stored as text, I cannot seem to convert the values into numbers. Can anyone assist?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Values stored as text

    Edit:
    I noticed that all your numbers start from position 3. In that case, you can use =RIGHT(A2,LEN(A2)-2) formula to extract the numeric values.

    Your data has an encoding issue. Save your Excel worksheet as a CSV File, close and reopen the CSV file. Then use substitute function to remove the unwanted characters.
    Attached Files Attached Files
    Last edited by JieJenn; 08-09-2021 at 09:42 AM. Reason: better solution

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Values stored as text

    if a one-off exercise, one option...

    enter: =LEFT(A2) in B1, copy B1, highlight column A, Edit -> Replace... paste the copied value into "Find What" (will appear blank) and leave "Replace With" empty, then hit Replace All

+ 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. Values from userform being stored as text not numbers
    By Marchenrysaad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2018, 07:31 AM
  2. [SOLVED] Dates stored as Text and Values - convert entire column to date?
    By happydays886 in forum Excel General
    Replies: 12
    Last Post: 01-05-2018, 12:13 PM
  3. [SOLVED] Sumif on Values stored as text and Criteria need wildcard.
    By timmtamm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2017, 01:15 PM
  4. Replies: 2
    Last Post: 06-19-2017, 04:51 AM
  5. [SOLVED] Pasting Values, Number Stored As Text
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-23-2014, 10:16 AM
  6. [SOLVED] Copy And Paste Value As Text (Stored As Text)
    By calvinle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2014, 02:23 PM
  7. [SOLVED] Marco to convert numbers stored as text to numbers and dates stored as text to numbers
    By a2424 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-22-2014, 10:19 AM

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