Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Convert random numbers to text..
HAPPY EVERYTHING!!
I have a problem and after searching for an answer for at least 3 hours I am stumped. I have a spreadsheet formatted as follows: Row B2 -- DT2 is the name of Regions. ColumnA3 down to A50 are products. In the cells to the left os A3.. and beneath each Region there are numbers ranging from 1-- 70000(sample). I need to replace all cells which have a value and are not empty with an 'X'. I then have a SQL script to extract that data in to a db. I have found many macros, scripts for converting to monetary values to text in just about any language, various vlookup and other conversion scripts but can not figure out how to do the above.. I would really appreciate any assistance!!!! TIA!! Raymond |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Convert random numbers to text..
I take it that you have a range and you want Excel to look at every cell in
that range, and if the cell is not empty and it has a number in it, replace whatever is in the cell with "X". If that is what you want, the following macro will do that. Note that you need to define (Set) the range MyRng in the macro to the range you want. As written, the macro sets the range to the used range of Column A from A1 down. HTH Otto Sub Test() Dim MyRng As Range Dim i As Range Set MyRng = Range("A1", Range("A" & Rows.Count).End(xlUp)) For Each i In MyRng If IsNumeric(i) And Not IsEmpty(i) Then _ i.Value = "X" Next i End Sub "rncolon" wrote in message oups.com... HAPPY EVERYTHING!! I have a problem and after searching for an answer for at least 3 hours I am stumped. I have a spreadsheet formatted as follows: Row B2 -- DT2 is the name of Regions. ColumnA3 down to A50 are products. In the cells to the left os A3.. and beneath each Region there are numbers ranging from 1-- 70000(sample). I need to replace all cells which have a value and are not empty with an 'X'. I then have a SQL script to extract that data in to a db. I have found many macros, scripts for converting to monetary values to text in just about any language, various vlookup and other conversion scripts but can not figure out how to do the above.. I would really appreciate any assistance!!!! TIA!! Raymond |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Convert random numbers to text..
Select that range (whole column?)
Edit|Goto check Constants and uncheck Text, Logicals, Errors (leave Numbers checked) click ok Type X and hit ctrl enter. rncolon wrote: HAPPY EVERYTHING!! I have a problem and after searching for an answer for at least 3 hours I am stumped. I have a spreadsheet formatted as follows: Row B2 -- DT2 is the name of Regions. ColumnA3 down to A50 are products. In the cells to the left os A3.. and beneath each Region there are numbers ranging from 1-- 70000(sample). I need to replace all cells which have a value and are not empty with an 'X'. I then have a SQL script to extract that data in to a db. I have found many macros, scripts for converting to monetary values to text in just about any language, various vlookup and other conversion scripts but can not figure out how to do the above.. I would really appreciate any assistance!!!! TIA!! Raymond -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Convert random numbers to text..
thank you very much for your very FAST responses.
I will be testing both out shortly and reply!! Happy EVERYTHING!! R |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Convert random numbers to text..
Both suggestions worked..
Thank you very much!!! R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to numbers | Excel Discussion (Misc queries) | |||
How do I convert numbers to text like bahttext but in sterling | Excel Worksheet Functions | |||
Convert Numbers to Text | Excel Worksheet Functions | |||
convert numbers to text. | Excel Worksheet Functions | |||
Convert text to numbers | Excel Discussion (Misc queries) |