473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

replacing cell content in excel

mmwit
15
Dear All,
I have an excel sheet which contents more than thousand records, and I want to replace one of the columns contents

what actually I want to do is.

that column contains phone numbers that has to be start by zero (0) but its been inserted without,
so I want to know if there is any way I can add 0 at the begining of every cell at ones,

because updating these all thousand records needs much time to update.

Thanks
MMWIT
Feb 13 '13 #1

✓ answered by NeoPa

In case that isn't clear you would :
  1. Insert a new column next to the column with your data in.
  2. Assuming you start at Row #2 put this formula in Y2 (We'll call the original column the data is in Column X.) :
    Expand|Select|Wrap|Line Numbers
    1. ="0" & X2
  3. Propagate this formula all the way down column Y for as many rows as you have data. You can Copy Y2 then select Y3 - Ynnn and Paste.
  4. Now copy and paste the results into X. Select Y2 - Ynnn and Copy. Move to X2 then Paste Special selecting Values.
  5. Now the update is complete it's safe to delete column Y.

7 2229
Rabbit
12,516 Expert Mod 8TB
What database are you using? I need to move this question to the correct forum as this is not a question about career advice.

Yes, you can use an update query to do that.
Feb 13 '13 #2
mmwit
15
this is in excel Rabbit
Feb 17 '13 #3
Rabbit
12,516 Expert Mod 8TB
Your thread has been moved to the Access VBA forum as that is the closest match to Excel.

What you can do is create a column where the formula prepends a 0 and the format of the cell is text. You can then copy that column and replace the original by copying only the value.
Feb 17 '13 #4
NeoPa
32,556 Expert Mod 16PB
In case that isn't clear you would :
  1. Insert a new column next to the column with your data in.
  2. Assuming you start at Row #2 put this formula in Y2 (We'll call the original column the data is in Column X.) :
    Expand|Select|Wrap|Line Numbers
    1. ="0" & X2
  3. Propagate this formula all the way down column Y for as many rows as you have data. You can Copy Y2 then select Y3 - Ynnn and Paste.
  4. Now copy and paste the results into X. Select Y2 - Ynnn and Copy. Move to X2 then Paste Special selecting Values.
  5. Now the update is complete it's safe to delete column Y.
Feb 18 '13 #5
mmwit
15
Thanks for your perfect Answer,

many thanks and
and appreciates
Feb 18 '13 #6
NeoPa
32,556 Expert Mod 16PB
Pleased to help. That concept is one that is used very frequently in Excel. So many things need data prepared with formulas but only the data itself saved.
Feb 18 '13 #7
ADezii
8,834 Expert 8TB
If you wanted to avoid the addition of a New Column and its subsequent Deletion, but wanted to simply Update Values in the existing Column by Prepending a '0' to each Value, you could use this simple Code to do the trick:
Expand|Select|Wrap|Line Numbers
  1. 'Assumes Column 'E' contains 1,000 Values from in the Range E1:E1000
  2. Dim intRowCtr As Integer
  3. Dim strCellValue As String
  4.  
  5. '5 - The Column containing Data ('E')
  6. '1000 - Number of Rows in Column 5 ('E')
  7.  
  8. For intRowCtr = 1 To 1000
  9.   strCellValue = Worksheets("Sheet1").Cells(intRowCtr, 5)
  10.     'Prepend a '0' to the Cell Value
  11.     Worksheets("Sheet1").Cells(intRowCtr, 5) = "0" & strCellValue
  12. Next
P.S. - The Column in question must be Formatted as Text in order for this approach to work effectively!
Feb 18 '13 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Peter Smith | last post by:
Hello, how do I put a value in a cell in Excel XP I'm programming an add-in, so all the variables (excelApp ..) work, I'm just putting the code snipplets here. click on the Add-in button in the...
0
by: elime | last post by:
Hi all I have a strange behaving on some PC with my DataGrid. It only occurs on some PC, on others it works perfectly fine. ->(same ..net version installed) it's very confusing. starting...
2
by: Maziar Aflatoun | last post by:
Hi, I have a datagrid which binds to a datareader dgXXXXX.DataSource = rdr; dgXXXXX.DataBind(); On dgXXXXX_EditCommand I can read the value of the selected row( e.Item.ItemIndex) which...
1
by: PCK | last post by:
I am trying to select a single cell from an Excel workbook. I am using the following code. strCN = GetExcelConnection("C:\Test\Excel.xls") strCMD = "select * from " oleDbCN = New...
0
by: ane1717 | last post by:
I am trying to write a code that runs when the user clicks a picture within another excel spreadsheet. I need it jump to a specified cell by searching for the contents within the cell "152Z33XX". I...
16
by: alexia.bee | last post by:
Hi all, In some weird reason, excel instance won;t die if i remove the comment from 4 lines of setting values into struct. here is a snipcode public...
1
by: BaaZauq | last post by:
Hello everybody! I'm not a master of clientside JavaScript but I have much working knowledge of it. Here is my problem : I have a web-page with a "text-area" box. I have an excel file with 10...
1
by: xavierrangel | last post by:
hey guys I'm trying to change the backcolor of a cell in excel using vb6.0 . at first I changed the size like this obj1.Columns(3).ColumnWidth = 15 but now I need to change the color thanks...
1
by: Monusonu | last post by:
Hi Expert, I am trying to get the value from excel formula cell using POI. My code works fine for less complex formula cells, but fails or returns error code for complex formula cells. Following...
2
by: mfletcher | last post by:
Hi All I have a formula in a textbox, like ='Flow 0700'!C38 and this causes the text in the textbox to populate with whatever is in cell C38 on worksheet 'Flow 0700' Like a regular formula linking...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.