numbers as text

I have cells that contain multiple values as a full string: Apple - Banana - 000123 When I convert to three columns using the "text to columns" operation, the third column converts to a number and removes the leading zeros (cell value turns into 123 as a number). This is breaking my lookup...

Hi, We export data into a workbook, all of the product codes are 4 digits but they are in text format. When i go to do lookups they dont work unless I paste the product codes into notepad and then back into word again. So 2 questions here I suppose: 1. Is there an easier way to do this rather...

Hi, I have IDs in Column A stored as Text (data is both numbers and text). To get unique IDs, I used an advanced filter and the results are in Column D. Problem is that the results show duplicates. For example, 130 shows twice. One has the error message - number stored as text- on the...

In spreadsheet 1 I have a list of codes ('00001, '00045, 'J1364 etc - not all are numeric). They are displaying fine as Text in xls 1. I need to essentially copy them to xls 2. I run the following code: oWB1.Sheets(1).Range(Cells(r1, cPaste).Address, Cells(r1 + rHeight...

Hello, I am wondering if there is a way to find the max of a range using VBA, I have thought of using the "max" worksheet function, but the problem is I have a range where the numbers are stored as text. I could convert the text to numbers and then run the max function but I would like to keep...

I am trying to write a macro that I will use to insert vlookups into a number of different workbooks. The problem I am having is that the lookup array is composed of numbers stored as text in the source material, while the lookup values are stored as "general" in the workbooks in which I am...

Hi Guys, I have a spreadsheet I want to use a SUMIF on, but column I want to sum is stored as text. I don't want to have to convert the column to text, I wanted to do it using a formula, but can't quite get the array formula right and hoping for some help Sheet one is something like the...

Hello everyone, I need to sum all the numerical values (the numbers before the letter) in column A based on what letter they have. So, the result for "a" would be 3.5, "b" would be 3 etc. (as below in red) Notes and restrictions: The only possible numerical values are :1 or 2 or 0.5. Letters...

Hi All, I'm sure this is a common theme on this board, but numbers appearing as text is causing me a helluva problem. I'm running an awkwardly complicated series of if statements with vlookups matching property codes in the first column and returning data from second column (see sample data...

I'm working on a spreadsheet to calculate production costs for jewelry design. I have 3 sheets corresponding to different components of a finished piece (e.g., chains, findings, charms), with basic math formulas diving the total cost by the total units to calculate the price per unit. There...

Hi Guys, I have a basic formula in one of my columns in a query. Its saying 'if there was an order on this date, put the order number in this column'. Easy peasy. Except the order numbers are being pulled from excel via a linked table. And they INSIST of being outputted back to excel as text...