I have a simple sheet that will require some names in a column. Whenever I try to add a name I get an error message saying " it must be a date greater than or equal to 1/2/1904". Obviously I have formatted this column incorrectly, I have tried to highlight the column, right click Format Cells, text but no luck.
View 7 RepliesI have a piece of code that imports data from a database on the web. It works but the import uses the wrong encoding. Excel also messes up a column with id-numbers, starting with three zeros that are removed by Excel.
How can a change my code so that it imports the data as utf8 and the columns as text?
Code:
Sub csvAll_diva_update()
Dim url As String
url = "URL;http://kth.diva-portal.org/dice/csvAll?query=-publicationTypeCode:studentThesis&start=0&rows=20&sort=author_sort%20asc"
Sheets("DiVA_All").Activate
ActiveSheet.Cells.Clear
[Code] .......
In Text to Columns, is it possible to set the default column data format to TEXT?
View 1 Replies View RelatedI have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies View RelatedI am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/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 RelatedExample......
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!
I want to convert number format to text format, any formula ?
View 6 Replies View Relatedhow i convert text format into numbers format in vb.
Currently
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" formula...it ooes not sum up
I faced an error "number stored as text"..how to i convert it to numbers format in vb.
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.
Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.
I've almost gotten my routine finsihed but this is last remaining obstacle.
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)
On the attached example i have a list of fractions (in the format ?/???). However, i have a problem when the fraction is 6/4 or 4/6 as excel rounds the nominator and de-nominator down to the lowest value so these fractions become 3/2 and 2/3. For these two fractions only i don't want this rounding down to happen.
I am open to any suggestions, but i was thinking of some vba where on pressing a button it would run a macro that went through the cells in the column and if the cell value is 1.5 (3/2) then format the cell as text and enter the value"6/4". For the 2/3 fraction, i think you may need to say if the cell is greater than 0.66 and less than 0.67 because this would possibly be harder to exact as the number of decimal places involved.
How to format different numbers to the same format (as text). Mainly I need code that would format numbers like 25, 25.36 or 254.60 to numbers looking like this 000002500, 000002536, 000025460. They must be of nine digit length with the last two digits as decimals.
View 9 Replies View RelatedI can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
I have a few tasks which I have been doing manually because I do not know if there is a way to accomplish these tasks automatically via some excel formulas.
My first task requires me to take an address, for example: 1234 CAMBIE STREET, and format it such that it is properly capitalized (only capital letters in the front of words) like this: 1234 Cambie Street
My second task involves taking an address, for example: 1234 1st Avenue W, and rearranging it such that the direction is in front of the street name like so: 1234 W 1st Avenue. What makes this task potentially even more complicated is that not all the addresses I am working with require the rearrangement, so I can't simply have a formula that puts the last group of characters in front of the first group of characters because that may screw up addresses that don't require this formatting.
My third task demands that 2 columns of names are combined into one column, while also simultaneously placing a "&" between the names. For example: Jonathan Parkinson | Sarah Parkinson turns into Jonathan Parkinson & Sarah Parkinson. Now I do understand how to use a simple combining formula (=a1&" & "&b1) but it isn't that simple. The columns are not all filled with names. Some pairs may have no names, whereas others may have only one name. Because of this, the formula I used as an example will result in many instances where all I see is a "&", and other instances where I see the first name, followed by an unnecessary "&".
So far, for the majority of these problems, I've been using a combination of manual data input, and the replace function, but I really do hope there is a faster and better way to go about these tasks.
can you format msgbox? i want to centre/center some text
(also - mods...
....If are you new to the forum, or unsure,...
surely -
...If you are new to the forum, or unsure,........?
)
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
How do I conditionally format a cell if a particular word is within the text in that cell?
View 9 Replies View RelatedHow do i do a conditional format where if c3 text=red then a3 text=red
View 6 Replies View RelatedI have a date format to show Day of Week ( DOW )
using format: [$-C09]dddd in Cell A1 if Formula is = NOW ()
But if want to use cell B1 to show the day of week FROM A1, but in text as if I typed the DOW.
The reason is, when a new day occurs and I try to use A1 in email for example, it will show up as numerical date and not the actual worded DOW.
How can I convert the DOW from A1 to show the day as if it was a typed DOW in cell B1 ?
I have a data set which is in a date format of MM/DD/YY. I have converted it to YYYYMMDD. I now need to copy and permanently past the values so that the value within the cell always reads to result of the date in the format of YYYYMMDD.
Example:
-current = 07/01/08
-new format = 20080701 (cell still reads 07/01/08)
-would like the cell to read 20080701
My main goal is to bring this into Access and use this dataset with another data set, which uses the YYYYMMDD format.
I have attached excel, where date is mention in B Column but when I am formatting it, it is not showing in proper date format. I want format in eg 18-Mar-2013.
Book1.xlsx
is there a way to have a cell display a certain text if it is empty?
i.e. i would like all the blank rows in a section of my spreadsheet to have a grey fill and display the text "Please input your description here" if the cell is blank.
Column B has list of Balance Sheet items for business and Column D has associated values. I need to perform the following:
Look for following text and if it finds it then change the font size to 16, bold it,center align and make the associated cell in column D as mentioned below.
ASSETS ....change the cell color in column D to white (Invisible).
Cash and Bank Accounts
TOTAL Cash and Bank Accounts
Other Assets ....change the cell color in column D to white (Invisible).
TOTAL Other Assets
TOTAL ASSETS
how can I convert or get a numeric value in a time format?
Senario: Let's say I have 418 as number. This is what I have 418 minutes. I should get 2:58 (2 hr 58 mins).
I have delay accounting software that can dump to Excel, but it puts the duration in the format "xdxhxm". It will display only as many digits as required, ie if it's 5minutes it will show "0d0h5m". If it's an hour and 45 minutes, it will show "0d1h45m" etc. I'm looking for a formula that will convert these to minutes so I can sum the events.
I found a similar formula, but it only worked if there was a space between number and letter and I don't understand the formulas well enough to fix that.
Currently we do a lot of report crunching using Crystal Reports. We will then dump the data into an Excel sheet to run varying pivot tables or databases off of.
Often we will have a field that the formatting will not match up to anything in Excel. You can change the cell to Text, General etc. The main problem associated with this is running VLOOKUPs. The item we type in will not match up to the data imported from Crystal. We have to F2, the new data before it will match up which is a pain considering there can be anywhere from 30-1000 rows.
I'm trying to solve this problem in 2 ways. First can anyone tell me an easier way to do handle this?
Second, after trying many other things I thought about a macro that would automate the F2 process.