# Formula: Insert A Zero Between A Set Of Numbers

Mar 20, 2009
I need a formula to take a 6-Digit Number and change it into a 7-Digit Number. Probably a if statement would better because i have 7 digit numbers in the same column with 6-digit numbers. I need to insert zero between the 4th digit and the 5th digit of a 6-digit number. For Example:............

View 5 Replies
ADVERTISEMENT
Oct 12, 2010

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1

2

3

4

8

9

becomes

1

2

3

4

8

9

View 7 Replies
View Related
May 7, 2009

I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.

Here is the code i have but it wont even compile........

View 9 Replies
View Related
Jun 26, 2007

I'm using Excel 2007 and have a spreadsheet with 2500 rows. What I would like to do is insert the row number into a blank cell in each row. This will become a unique ID# for each row that I need to import this into a database later.

View 3 Replies
View Related
Jan 10, 2013

I would like to insert a space after a series of numbers using VBA, ie:

A00000ABC = A00000 ABC

A111AAA = A111 AAA

View 9 Replies
View Related
Jun 19, 2013

I have a column of part numbers in the format 530020109 but I would like to change to 530 0201 09.

View 4 Replies
View Related
Jun 22, 2013

I have a column of numbers that look like this: 111222111222111 and I need them to look like this: 111 222 111 222 111. The numbers are all random and not 111222.

Then I have a second column with numbers like this: 11112222111122221111 but they need to be like this: 1111 2222 1111 2222 1111.

View 5 Replies
View Related
Jan 30, 2014

I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.

ABCD90bj10r7

ABCD,90,bj,10,r,7

ABCD90rt8r7

ABCD,90,rt,8,r,7

iFH15jr7ri12

iFH,15,jr,7,ri,12

iFG155jr8ri11

iFG,155,jr,8,ri,11

iFG15jr8ri9

iFG,15,jr,8,ri,9

View 2 Replies
View Related
Aug 24, 2007

I have a column that contains all telephone numbers without any spaces - 01234567890

I need to have at least one space inserted anywhere in the number i.e. 0 1234567890 or 01234 567890.

View 8 Replies
View Related
Jan 7, 2009

I have a string of numbers ex. 1234, 5678, 91011 in 400 rows. Before each string I would like to add 88.

View 4 Replies
View Related
Aug 3, 2006

I am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:

Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

5 -------Part E

6 -------Part F

As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

4

5 -------Part E

6 -------Part F

I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.

View 4 Replies
View Related
Feb 19, 2008

The excel file has industrial information arranged by sector. The sectors are represented by six digit codes that are not strictly sequential. For example, a typical example of consecutive codes is 120011,120012,120030,120040,130011. There are upwards of three hundred sectors.

The data I am using is missing certain sectors - sometimes more than 30 at a time. I would like to find a quick way to identify the missing sectors and insert rows there, preferably with the first cell in the row being the missing sectoral code.

I have a similar problems with columns as well. But I can always just transpose the data and use whatever solution works for the rows.

View 9 Replies
View Related
Apr 4, 2008

I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................

With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!

View 5 Replies
View Related
Oct 3, 2008

Need a function that would insert a letter or a number in front of numbers in a cell for example

column A

3245

I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with

Column A

S3245

View 3 Replies
View Related
Jul 30, 2014

I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.

Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example

Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc

Cells A2:A6 contain the individual constants, a, b, c, etc

I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g

In cell B10: =a+b*A10+c*A10^2

I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.

View 2 Replies
View Related
Oct 7, 2009

I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.

My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator

View 3 Replies
View Related
Jan 29, 2008

I use this formula for converting date of birth to a number based on today’s date. I have tried to have the number.. 6yrs 6 months to print only 6 not 7 years old. How do I insert the rounddown formula into this formula

=IF((AD37=""),"",YEAR(AQ36)-YEAR(AD37))

View 9 Replies
View Related
Jan 20, 2009

I have a spread sheet that I need to insert rows into and then in those same rows, add formulas that display percentages compared to the grand total row. To make it a little more complicated, this spread sheet changes from day to day, so the rows and columns will be different each time. So the code will have to recognize text, then insert the row, then do the formulas in the blank row. The only constant will be that it will always start on Row 5. I have no idea how to start or begin to do this, so I attached a file with a page showing where I am at as an example, and then anther page showing what I want ....

View 10 Replies
View Related
May 31, 2014

What kind of a VBA code for inserting the formula such like this one

=INDEX('Bill'!$F$2:$F$510,MATCH(J2,'Bill'!$I$2:$I$510,0)) in multiple rows of the same column.

I'd like to insert it in column H of my sheet named = LIST in consecutive rows till the last data cell in column A.

View 2 Replies
View Related
Aug 31, 2008

I am wanting to use VBA code to insert the formula =IF(B8="","",VLOOKUP(B8,Information!C4:D200,2,FALSE)) into cell B9 (it is merged to form cell B9:B13).

I have the formula copied into cell M4 on sheet 3 and have tried to reference it but can't seem to do it. I am happy either using VBA to reference it or using a VBA code to insert the text into cell B9

How can i get the formula from sheet 3 into cell B9 in Sheet 1, or simply put the text in.

View 9 Replies
View Related
Jul 3, 2006

In the attached sample workbook, I am trying to insert the formula contained in "C2" down column "C" for each row of data. Is this possible using a Loop? Have I entered the code incorrectly?

View 7 Replies
View Related
Jan 4, 2007

Does anyone have a code for inserting a formula for every so many # of rows? In my case I have to insert a vlookup formula into every 66 rows, in column L.

View 9 Replies
View Related
Apr 14, 2014

All I want to do is insert a formula into a text box. This thread answers the exact same question but I don't understand how to highlight the text box as an object... [URL]....

View 2 Replies
View Related
Apr 23, 2009

I have a spreadsheet with data in the first few columns, then a few columns of different formulae which reference the data.

This spreadsheet is constantly getting rows inserted into it, and I'd like for the formulae to be copied into the new rows automatically, rather than having to copy/paste the formulae every time columns are inserted.

View 6 Replies
View Related
Nov 3, 2009

I'm using Excel 2000/2002. I have a workbook with 12 sheets named Jan, Feb, etc.

I want to add a new sheet (Report) with formulas in various cells to get data from a cell in a particular sheet.

For instance, in a cell of the new sheet is: =Jan!D64. I want the user to be able to select a month from a drop down list and for the formula to change sheets depending on the month selected. The formula should be: =(sheet name!)D64. I tried playing with INDIRECT, but maybe couldn't get the syntax right. I can't use macros, the Excel is on a server and they are not permitted. The end user is less knowledgeable in Excel than me.

View 2 Replies
View Related
May 1, 2014

I once heard that in order for a macro to insert a formula you must double all the quotations. Anyway, i did so in the following macro but i got an error message.

View 3 Replies
View Related
Nov 11, 2008

I'm trying to insert the following formula into cell AA11. For some reason I keep getting a 1004 Runtime Error. I'm assuming there is some type of syntax error. I'm not really sure what I'm doing wrong.

View 6 Replies
View Related
Jul 6, 2009

I have a header row, so everything should start at row 2.

If Cell A2 has data, I would like to use VBA to insert the following formula into B2 (this works fine when pasted directly into cell B2):

View 6 Replies
View Related
Mar 6, 2009

I have a simple AVG formula and I would like to insert the word "minutes" after the result.

I cant get it to work .... i know this is simple, but I am drawing a blank!

AVERAGEIF('producton 3'!E:E,"march 2008",'producton 3'!G:G)

View 2 Replies
View Related
Sep 26, 2011

I currently have some VBA code which is inserting

30 days

Cancelled

Down a column inside a list cell.

I am just wondering if there is a way to instead of saying 30 days to have the date 30 days from now?

Code I am using is:

Code:

Range("M6:M" & lngCounterC - 1).Select

With Selection.Validation

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="30 Days, Cancelled"

End With

View 1 Replies
View Related