I need to list 4 digit numbers in each section followed by commas, but whatever I do it goes to 3digits (e.g. I need "1234,5678,9123" and as soon as I hit Enter it goes to "123,456,789,123"). It wont work to format as text because I have a whole bunch of 12 digit numbers to break up into 4.
View 9 RepliesI am inserting data into my spreadsheet using VBA code to read a file and insert the data into the relevant cells. My problem arises when I have a string such as 80830410205724044. The actual code that inserts the data is as shown (temp is dimmed as string)....
View 9 Replies View RelatedI did post a problem where I have a number like 123456 and I need to have Excel change it so that it puts a comma before the last two digits .. like so: 1234,56
I got a reply where I got the solution to use 0","00 and this works in Excel (using the custom format)
The only problem is that although the number changes in the cell to 1234,56 it doesn´t do so in the FX window and thus when I use the number to multiply it is actually 123456 instead of 1234,56 like it want it to be.
I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.
=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)
I have a column with social security numbers, i.e. 555-33-2222 and I need to change to show only the last four digits, i.e. xxx-xx-2222. Can this be done in excel?
View 4 Replies View RelatedIf I enter 2.5 into a cell, and then change the format so that there are no digits after the decimal, the number changes to a 3.
2.4 correctly rounds to 2, but shouldn't 2.5 round to 2???
I thought that even numbers were supposed to round down, and odd numbers round up when there is a 5 right after the decimal.
I have a calculated field which is essentially two concatenated values (DDMMYY and sequential numbers starting from 1). I want it so that any single digits will automatically have a zero in front (e.g. 01, 02, 03 etc). The concatenating takes place in VBA so it has to be coded...
View 3 Replies View Relatedin A1 I have a drop down list with unit numbers and descriptions
example - 123456 - 30 to 150 lots
I want to see if when I pick from the drop down list the right unit and description that the cell will only show the first 5 digits.
example I pick 123456 - 30 to 150 lots in cell A1 would say 123456
I have a macro that creates an email based on the contents of each column in the worksheet. The macro works great, but I would like to format a column (A) which contains numbers into the Comma Style, prior to sending out the email.
Is there a VBA code that will format a given column (A) into a certain style (Currency, Comma, Percentage, etc) ?
Is there any way to change the decimal point from the usual period symbol (.) to a comma (,). The reason being, i have a structural design spreadsheet and the new terminology is just so, changed from 00.00 to 00,00 I have tried cell formatting but it just uses a comma (,) for numbers in their thousand and it wont do what i need.
View 3 Replies View RelatedI am having problems applying custom format to numbers that need to be formatted.
custom format use is 0",00". When I apply the custom format above to whole numbers, I achieve the results I want:
0.00
20.00
40.00
becomes
0,00
20,00
40,00
However, when I apply to numbers with decimal places
0.20.......
how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?
I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.
I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB.
Example numbers:
21130 & 21065
I want to check each number if EITHER of the two conditions is true:
1. if the third digit from the right (the hundreth place) is greater than zero;
or
2. if the second digit from the right (the tens place) is >=6.
If either is true I want to add a particular number to the original number.
My example numbers meet questions 1 & 2, respectively.
this may be simple but I need to convent say
A1 = 09
B1 = 23
into
C1 = 0
D1 = 9
E1 = 2
F1 = 3
..etc
I have a unique 6 digit number in a column. I have 2500 rows. I need to cut the first three numbers only leaving the last 3 numbers in the column for each row.
Example:
112345
234325
I would need 112, and 234 deleted. Checked the forum and cannot get quite what I want.
I have a column with numbers in about 500 rows. The entries are 5 numbers long and others 8. So I thought i could use one of the following: A macro code to tell a cell to delete the first 3 numbers if the entry is 8 numbers long?
OR
A macro code to tell a cell to reduce itself to 5 digits long starting from the right? Attached is a small example
I need a formula (a VBA script won't work, as it also has to work in a google docs version) that concatenates numbers taken from a specific range in a single row with ", " in between, but only if 2 conditions in the particular column those numbers are in are matched.
Let's say, my numbers are in Row 1, from B1 to N1.
My conditional values are in Rows 5 and 6 respectively, also from B to N.
Values in Row 5 are text strings (Names of people), Values in Row 6 are Dates.
I want the numbers from Row 1 to be concatenated, but only if a particular Name and a date from a specific month are in the same column.
Row 1: 1 2 3
Row 5: Name Name Name
Row 6: Date in Feb, Date in March, Date in March
Upon selection of a month and name in a dropdown cell menu, i want the output to look like this:
Name+March -> 2, 3
Name+February -> 1
make a macro wich it's going to sort comma delimited number in the correct order.. see attached file to get the wanted reult..
View 5 Replies View RelatedI'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
I want to put in each cell a Punctation mark (comma), like this: ,
... when I have in each cell a number
I don't know how;
How to convert any types format into Text with 5 digits in selection?
For instance, the content I select is "234", and I want it to convert to "00234". Just like the function "TEXT" in excel. How can I realize it promptly in VBA?
I need a way to display all 8 digit numbers that have the digits 1-8 in them. (ie. 12345678 but NOT 12345679 OR 12345677) Also If I could somehow divide by 13 then check for whole numbers
View 14 Replies View RelatedI would like to extract numbers based on the first 2 digits (22....) of a column along with its next column (on the right) and put them on another sheet.
View 9 Replies View RelatedI am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so
Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5
The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is
Card 1 / Cards 2 / Card 3
Player 1
Player 2
IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.
For example the cell contains 1,M2,M7,M1,M8:2,M15,M9,M4,M5:3,M3,M6,M14,M11,M12:4,M10,M13 and I need to create M1, M2 , M3 ..etc columns and attach the coresponding number to each one of them. For instance M2,M7,M1,M8will get 1 and so forth and so on. I was acutally thinking using the Notepad to replace the , with a TAB space and paste them back in to my data set and create some IF statements. On the other hand running VBA scrip would make it even easier. However I would preffer to use a function (that I am not 100% familiar with) and leave the VBA scrip out of the question for the moment.
View 12 Replies View RelatedI have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.
What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.
I have some numbers around 200 numbers which are not same digits i.e. if 1st number is 7 digits, 2nd number is of 10 digits.
Like this even if i have a number of 1 or 2 digits then all number should have equal digits i.e. in 200 numbers if one numbers is of 13 digits then all the numbers should be 13 digits.
To do this first i am doing this to the right align & comparison all numbers & taking the highest digit number & pre-fixing the other numbers with zeros’ so that all number are equal in number of digits. This is taking huge time.
I want to make a macro or any easy method so that all should have equal no of digits.
I want that if 200 numbers are pasted in the column E i.e. from E2 to E201 then all should be converted to equal digits number by prefixing zeros & should appear in column G from G2 to G201.
And after converting them it should be prefixed with D/ OR D/ABC:
This will be present in B2 cell i.e. this cell will be blank always. And once I have any thing in that cell that should be prefixed to the converted numbers in the column G & they should appear in Column I.
And again the numbers in column G should be prefixed with the content in B2 & should appear in column K in the Ascending order sorted.
Suppose if , I have 500 numbers then they should be converted automatically to equal digits & appear in Column G & prefixed with the contented in cell B2 should appear in Column I & sorted series in ascending order with prefixed with cell B2 in the column K.
And if I have 15 numbers only then it should do the same job easily. Here the numbers of numbers are not defined.
All this should be get done on click of the command button.
I have a macro that copies long decimals (11 places) from an Excel file to a CSV file. However, the long decimals often get truncated from 11 places down to 3 or 4. I've tried a number of different coding methods to combat this, the most recent and most successful of which is shown below
Application.Workbooks.Open Filename:="C:...2008_alldata.csv"
Cells.Select
Selection.ClearContents
ActiveWindow.ActivateNext
Application.Goto Reference:="AllDataTable"
Selection.Copy
ActiveWindow.ActivateNext
Application.Goto Reference:="R1C1"
Selection.PasteSpecial Paste:=xlPasteValues
Range("R2:BI5000").Select
Selection.NumberFormat = "#,##0.00000000000"
ActiveWorkbook.Save
ActiveWindow.Close savechanges:=False
However, sometimes this method also fails. Is there a better way anyone knows of to ensure that the entire decimal is copied?
When the entry in a cell is of the format e.g 3,4 is there any way to find the value 3 or 4 in that cell?
Attempts like:
Cells(i, 1).Value = variable (e.g variable=3)
or
Set cell_found = Cells.Range("A1:A10").Find(variable, lookat:=xlWhole)
do not find the cell with the 3 (or 4). The above work fine when 3 or 4 are alone in the cell. Is there anyway to find these values in the cell?
I basically need to grab all the numbers going down in a row and get them into one cell in Text format with a comma between them.
A1
5293
5294
5295
5296
5299
5300
5301
5302
5303
5304
5305
B1
What I need in Text format
5293,5294,5295……