Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding text to column cell if there's a value present
I have a column of about 3000 cells...some of them are empty and some have a
number in them from 1 to 4 digits (21 , 454, 1001, 2) etc. These are address/unit numbers. I'm trying to write a function that will check each cell and if there's a value in it...write Unit 'value' so I get Unit 454 , etc. If the cell is empty...then there's no "Unit" text added ? Been trying for awhile but don't seem to be getting close...any ideas on this type of function ? Thanks, Tim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding text to column cell if there's a value present
Hello Tim
Two ways; 1 add a helper column and assume that the numbers are in column A type the formula =IF(A2="","","Unit "&A2) and copy down. Select the formulas and press ctrl & C (copy) then Edit, Paste special and choose values. The move the new values over the originals. 2nd use a macro. Copy the code into a module in VB Editor, return to Excel, select the column and choose Tools, Macros and run the macro. - this will make the changes in situ Sub WriteUnit() For Each c In Selection If c = "" Then 'do nothing ElseIf IsNumeric(c) Then c.Value = "Unit " & c End If Next End Sub Regards Peter "Tim R" wrote: I have a column of about 3000 cells...some of them are empty and some have a number in them from 1 to 4 digits (21 , 454, 1001, 2) etc. These are address/unit numbers. I'm trying to write a function that will check each cell and if there's a value in it...write Unit 'value' so I get Unit 454 , etc. If the cell is empty...then there's no "Unit" text added ? Been trying for awhile but don't seem to be getting close...any ideas on this type of function ? Thanks, Tim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding text to column cell if there's a value present
In addition to Billy's excellent suggestions, you can select the cells and:
Format Cells... Number Custom General" Unit" This adds the text only if the cell is not empty. The cells can also be added, etc. just like other numbers. -- Gary''s Student - gsnu2007 "Tim R" wrote: I have a column of about 3000 cells...some of them are empty and some have a number in them from 1 to 4 digits (21 , 454, 1001, 2) etc. These are address/unit numbers. I'm trying to write a function that will check each cell and if there's a value in it...write Unit 'value' so I get Unit 454 , etc. If the cell is empty...then there's no "Unit" text added ? Been trying for awhile but don't seem to be getting close...any ideas on this type of function ? Thanks, Tim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding text to column cell if there's a value present
Neat one Gary!
I would never have thought of that, and Format Cells... Number Custom "Unit "General will place Unit before the number, Who would have... Peter "Gary''s Student" wrote: In addition to Billy's excellent suggestions, you can select the cells and: Format Cells... Number Custom General"Unit " This adds the text only if the cell is not empty. The cells can also be added, etc. just like other numbers. -- Gary''s Student - gsnu2007 "Tim R" wrote: I have a column of about 3000 cells...some of them are empty and some have a number in them from 1 to 4 digits (21 , 454, 1001, 2) etc. These are address/unit numbers. I'm trying to write a function that will check each cell and if there's a value in it...write Unit 'value' so I get Unit 454 , etc. If the cell is empty...then there's no "Unit" text added ? Been trying for awhile but don't seem to be getting close...any ideas on this type of function ? Thanks, Tim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding text to column cell if there's a value present
I like it too. We get to combine numbers with text and still use them as
numbers! -- Gary''s Student - gsnu2007 "Billy Liddel" wrote: Neat one Gary! I would never have thought of that, and Format Cells... Number Custom "Unit "General will place Unit before the number, Who would have... Peter "Gary''s Student" wrote: In addition to Billy's excellent suggestions, you can select the cells and: Format Cells... Number Custom General"Unit " This adds the text only if the cell is not empty. The cells can also be added, etc. just like other numbers. -- Gary''s Student - gsnu2007 "Tim R" wrote: I have a column of about 3000 cells...some of them are empty and some have a number in them from 1 to 4 digits (21 , 454, 1001, 2) etc. These are address/unit numbers. I'm trying to write a function that will check each cell and if there's a value in it...write Unit 'value' so I get Unit 454 , etc. If the cell is empty...then there's no "Unit" text added ? Been trying for awhile but don't seem to be getting close...any ideas on this type of function ? Thanks, Tim |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding text to column cell if there's a value present
See the replies to your post of a couple of hours earlier than this one.
-- David Biddulph "Tim R" wrote in message ... I have a column of about 3000 cells...some of them are empty and some have a number in them from 1 to 4 digits (21 , 454, 1001, 2) etc. These are address/unit numbers. I'm trying to write a function that will check each cell and if there's a value in it...write Unit 'value' so I get Unit 454 , etc. If the cell is empty...then there's no "Unit" text added ? Been trying for awhile but don't seem to be getting close...any ideas on this type of function ? Thanks, Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding text to column cell if there's a value present | Excel Discussion (Misc queries) | |||
adding up number of people present at certain times | Excel Worksheet Functions | |||
Need to parse column data only when text is present | Excel Worksheet Functions | |||
Forgotten a Simple Forumla - Is Text present in another column | Excel Discussion (Misc queries) | |||
SUM/COUNT column(s) based on specific value present within the column | Excel Worksheet Functions |