Convert The Digit In Specific Format

May 6, 2009

I work in the flooring industry and the format we use for cuts of carpet/vinyl is 40-3 (for 40.25 feet), etc. So we are trying to create an excel spreadsheet that will add up these cuts. Right now it's kind of a mess because we have decimals in a column we will be hiding and are converting that to our industry format.

The formula I am having trouble with is as follows:

The problem is that whenever the totals end in zero, the formula won't convert 40.00 to 40-0. It gives me an error every time. If the total is 40.25 it will convert it to 40-3 just fine.

View 7 Replies


Automatically Convert 4 Digit Number To Time Format

Aug 30, 2006

Is there any way that I can have a column of cells change the entered 4 digit to a time format? E.g. when somebody enters 1212 in a cell it will automatically convert the entered numbers to 12:12

View 9 Replies View Related

Convert From A Specific Date Format

Jan 7, 2009

If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?

View 4 Replies View Related

Convert A Date In Specific Format

Oct 19, 2009

I'm trying to convert a date in the following format: 19970121 to 1/21/1997 and am having trouble getting rid of the leading zero in the final version. I also need it to pick up the 2 digits in months that have them from 9 (Sept) through 12 (Dec), though, for use in a VLOOKUP function. Can this be done at all?

Here is the code I've been using so far that doesn't entirely work for what I need it to do:


where B2 refers to the column the original date form (19970121) is in. I'm still stuck with the leading zero in this code.

View 9 Replies View Related

Convert 4 Digit Numbers To Text Value

Feb 21, 2014

I have a column that contains 4 digit numbers that I want to convert to a text value. Here is my example

Number Text Value
7004 RBPA
7002 DCVA
7001 PVBA

There is a total of 10 different number values. I want to include the conversion vba into an existing macro that is performing other functions on the spreadsheet.

View 1 Replies View Related

Convert 4 Digit Numbers To Times

Jul 16, 2007

Is there any way changing general four digit number into hours and minutes?

I have almost 31000 rows of imported data, showing arrival and departure times with four digits (dep 2130, arr 0130) without colons and I need to subtract dep from arr to get block hours. Is there a way to add colon to multiple cells or some other solution to my problem?

Adding colon to 62000 cells one by one does not sound that good to me.

View 7 Replies View Related

Convert X Digit Number To Letters

May 11, 2008

Nice to meet you all. I'd be grateful for any help I could get on this as I have tried it and I'm a bit stumped...

What I need to do is the following:

Convert a 4 digit number (e.g. 1234) in a single cell to a 4 letter string (e.g BCDE) and have the output appear in another cell.

The conversion should be as follows:


So, for example, 3678 in one cell should be converted to DGHI in the target cell.

View 6 Replies View Related

Automatically Convert Date Into Single Digit?

Jan 3, 2012

I'm looking for a formula that may automatically convert date into single digit e.g today is Jan 03, 2012 & after adding (1+0+3+2+0+1+2) it will come out 9 ....

View 6 Replies View Related

Change/Convert 4 Digit Number Into Time

Dec 24, 2008

Is it possible to enter a 4-digit number (i.e. 1400) into a textbox and have it display 14:00 and have the time entered into a cell?

View 3 Replies View Related

Convert 4 Digit Zip Code To 5 Digit Zip Code?

Apr 25, 2014

I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:

Waltham MA 2453
convert to 'Waltham MA 02453'

Boston MA 2210
convert to 'Boston MA 02210'

CEDAR GROVE, NJ 7009-1174
convert to 'CEDAR GROVE, NJ 07009-1174'

convert to 'TEANECK CITY, NJ 07666'

View 3 Replies View Related

Add Specific Number In A Digit?

Jun 9, 2014

Well I want to add few numbers in a few number for example I have ten columns containing few cell numbers and i want to add 92 my country code with it. I have tried this by using replace formula but in result it comes in text format from which i cannot use another formula unless text format is removed and for that i have to do it one by on in every single cell. I have attached a sample sheet.

View 4 Replies View Related

Converting 4 Digit Number Into Time Format

Jan 15, 2014

I have two Columns of 4 digit numbers (24 hour clock format). I need these numbers to be in Time Format. Formatting the Columns or even individual cells seems to do nothing.

1238 as 12:38 etc.

These numbers are acquired using VLOOKUP

I have attached a copy of my file, The numbers in question are on sheet "iCalender" 8th sheet accross.
Columns "start Time" and "End Time"

Time Format is Compulsory so that I can upload to a .ics Web Converter. Of which is currently failing for this reason.

5th January 2014 - Copy.xlsm

View 13 Replies View Related

How To Format Numbers To Show Actual First Digit

Aug 5, 2013

How do I format numbers so it will show the actual first digit, and the rest as x?

so that $1,234.56

will appear as $1,xxx.xx

or alternatively,


will appear as $1,xxx,xxx.xx

I would prefer to do this with custom formatting rather than VBA,

(this is so I can generate sample reports for prospective clients, without showing the real numbers in the tables)

View 1 Replies View Related

Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format

Jun 7, 2006

I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.

View 7 Replies View Related

Macro Or VBA - Search General Format Convert To Date Format

Oct 26, 2012

Every month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.

For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.

What I need is: Search in the A column, if date format leave it , if not change from general to date format.

View 2 Replies View Related

Convert Data In Invalid Format Into Correct Format

May 1, 2014

I have around 30k data. which is in invalid format.

Ex: 12987654321vinay kk 876543219
32567456789 kkccjhg fo 345678921

I want to convert this into correct format as below with start letter from

MOD, 987654321,, vinay, kk,87654219
MOD,567456789, , kkccjhg, fo,345678921

I want to know which are formulas I have to use to get this info in correct format.

View 1 Replies View Related

Seperate 2 Digit Number Into 2 Single Digit Numbers

Apr 3, 2008

I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?

View 4 Replies View Related

Convert Number Format To Time Format

Nov 28, 2013

How do I convert 11.20.00 in A1 to 11:20:00 in B1?

I've tried =TEXT(A1,"hh:mm:ss") to no avail.

View 3 Replies View Related

Convert A Date Format To A Text Format

Oct 6, 2008


In D2 I have: =B2
In D3 I have manually input: (space)3-08

As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!

View 3 Replies View Related

Convert General Format Into Date Format?

Mar 23, 2012

How we can convert any data which is in General format in to date format.For ex I've:

19/03/2012 14:57 (General format)

I want the output/result


View 5 Replies View Related

Convert Number Format To Text Format?

Oct 11, 2012

I want to convert number format to text format, any formula ?

View 6 Replies View Related

Convert Text Format To Numbers Format In Vb

Nov 21, 2006

how i convert text format into numbers format in vb.

i have a formula in vB:

Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
End Sub

however..when i sum it up (in excel) using"=sum" ooes not sum up

I faced an error "number stored as text" to i convert it to numbers format in vb.

View 9 Replies View Related

Format Cell To Always Insert Specific Thing When Specific Name Is Typed?

Apr 4, 2013

Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?

This has seemed to have happened on a computer at work.

In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"

If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.

View 6 Replies View Related

Convert Date Format To Another Format

Sep 11, 2007

How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?

View 9 Replies View Related

Macro To Force Format And Check Barcode Check Digit

Nov 27, 2009

My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts

8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"

these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is

View 9 Replies View Related

Format Number To Text With Specific Format

Sep 14, 2007

I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:

474556788 to 047-455678-8

Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)

View 10 Replies View Related

Check Digit For 8 Digit Number

Jun 13, 2014

How to calculate a check digit in excel. The details to calculate this are as follows:

All variables in the calculation are positive integers.

We take each integer of the pro number and multiply it by a value and sum them to get a total.

An Example: 8 Digit Pro Number: 66988757
Pro Number: 6 6 9 8 8 7 5 7
Position in the Pro Number: 1 2 3 4 5 6 7 8
Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.

Using our example pro again:
6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8)
6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2
54+ 48+ 63+48+40+28+15+14 = 310
Take the Sum of the previous calculation and divide it by 11
310/11 = 28

(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).

Figure a remainder by multiplying the quotient by 11 and subtracting from the sum.
Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2
Check Digit = 11- Remainder
Check Digit = 11 - 2 ---> Check Digit = 9
Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.

View 3 Replies View Related

VBA Convert Format

Oct 23, 2009

I have a spreadsheet that in various cells have numbers that have been been formated as text and have a leading zero and therefore displays the green flag in the corner of the cell. I want to change them to number format and clear the green flag.

using something like
Cells.NumberFormat = "0.00"

doesn't seem to work. whist it shows the format as number it still leaves the green flag and the leading zero.

View 9 Replies View Related

How To Convert A Date Format

Jan 23, 2014

I have a column with dates like:

Jan 22 2014
Dec 7 2013
Mar 17 2013

which I want to convert to a more standard (sortable) format.

Maybe 1/22/14

View 4 Replies View Related

Convert Date Format

Mar 5, 2009

I have imported date field values from ClearQuest(IBM Product) into Excel worksheet. after import the data display format in the excel looks like

Thursday, February 19, 2009 9:11:42 AM

Now I need to calculate the age of the defect in number of days by using the formula

=NOW()-J26 where the cell J26 contains the above data . However I always get the display as #VALUE in the cell.

I changed the field format for cell J26 to custom dddd, mmmm dd, yyyy h:mm:ss AM/PM but still does not work.

My question now is- how do I convert dddd, mmmm dd, yyyy h:mm:ss AM/PM to mm/dd/yyyy h:mm:ss AM/PM so that I would be able to find the age of the defects?

Or is the a way to change the format for NOW() to something dddd, mmmm dd, yyyy h:mm:ss AM/PM?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved