I've a book with 13 sheets, I use sheets number 2 to sheet number 13 to paste data from a webpage as Text. Then that data is used on sheet number 1.
I'm having problems using the numbers coming from the pastes, in the webpage some come in fractions (4/11, 8/3, etc). I made a macro to change those cells from Text to number, but that doesn't make them usable as numbers when I refer them in sheet1. To make them usable, after changing the format from text to number, I've to double click on each cell I want to refer and just hit Enter, then the content changes, e.g, from 10/3 to 3.33. And then I can use the data as a number on sheet number1.
The vbs code is the following, it does the data pasting as text allright, but I don't know how to make usable those individual cells I was refering to before. Besides doing the work by hand.
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.
I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
I 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 macro which will import data to the worksheet, then perform some formatting on the data, then assign the month & job description based on the lookup table. The problem is that when I import in the data, the data in column B&C will be store as text instead of number and the date in column E will store a 2 digit year instead on 4 digit year which cause error to my macro. I have try to preset the column format to number, i even try to change the column format to number when i run the format macro data. But the problem is still there.
What If we had to replace any number.. Lets say, if we had to seperate NUMBER TEXT NUMBER in different combinations.... B2 contains values like these then
TOM CRUISE 12 TOM 5879 CRUISE TOM CRUISE 123456789 123456789 TOM CRUISE 123 TOM CRUISE 456
[ = SUBSTITUTE(B2,"1234567890","") ]
I am at my wit's end pondering over it?
How to make the SUBSTITUTE function work for each individual digit?
I get a dump that is in the form xxxx gbps or xxxx mbps (gigabits and megabits). I'd like to either use a formula or VBA code to convert this to mbps in another column.
I have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula
e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is $" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in $-600000000. I'd like it to look like $(600,000,000).
I have a couple of excelfiles in which someone made a flowchart with text boxes. I would like a search code that would help me find a text or number in those text boxes. The CTRL+F function only works on cells. Does something similar exist for text boxes? Or can this be made in a macro?
I have an excel spreadsheet of which one column contains product descriptions and tracklistings in each cell. I upload the spreadsheet to a website where the product description and tracklisting uses html. I need to add a line break to the tracklisting. So currently in my product description I have something like this;
Code:
Boston streetpunk!!! Hard, raw, still melodic and with very good vocals! The band did many sampler contributions in the past... Rebellion presents you their first official full-length album with 12 great songs in total!!!... Tracklist: 1. Thomas P Cullity 2. Jinx 3. Go For Broke 4. Last Chance 5. Boulevard Of Broken Dreams 6. Takin' Over 7. Burn 8. Another Round 9. Hell To Pay 10. Street Rule 11. Taken Away 12. City Rats but I need it to look like this;
Code:
Boston streetpunk!!! Hard, raw, still melodic and with very good vocals! The band did many sampler contributions in the past... Rebellion presents you their first official full-length album with 12 great songs in total!!!... Tracklist: 1. Thomas P Cullity 2. Jinx 3. Go For Broke 4. Last Chance 5. Boulevard Of Broken Dreams 6. Takin' Over 7. Burn 8. Another Round 9. Hell To Pay 10. Street Rule 11. Taken Away 12. City Rats
How to get a number in a cell converted into text in another cell? e.g A1 has number 28, I want to have the number (28) as text like this (Twenty Eight) in another cell.
I currently have a spreadsheet which I use to do an audit of the office stationery supplies.
There 3 columns at the focus of this question:
F - Number of items in stock H - Stock re-ordering level I - Order Required
I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.
The statement is =IF(H1>=F1, "Yes","No")
This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.
Any value I enter in F will cause the I to say "Yes".
Is there any way to make the word "Special" equivalent to 0, so I says "No"?
I have a combobox embedded into an excel sheet with a linked cell and a range defined in the combobox properties. Every time I change the value in the combobox, the value in the linked cell is stored as text instead of a number.
I have tried copying a cell containing 1 and copying and pasting as special into the linked cell but that only fixes it for the time being. If I change the value in the combobox again, the value in the linked cell is again stored as text. I tried =value() in the linked cell but that again only worked temporarily. Every time I change the value in the combobox, the value in the linked cell is stored as text.
I have a list of phone numbers in which name of the person and phone numbers are joined together. I would like to separate the name and phone number. Is there any way to do that. Example: I have the text like George9898989898, now i want it to appear as George 9898989898.
I am currently trying to find an easy way to convert cell references to number format. The issue I'm having is when compiling a small formula (CONCATENATE) as part of a macro in order to produce a unique reference number to a column of data (1000's of cells long) I come accross the age old number stored as text issue after the macro has converted the formula to values.
Have tried editing the macro to number stored as text = false which removes the annoying little red triangle but the vlookup functions still wont read the values. My current solution is to highlight the entirity of the data and convert to number which takes a long long time as there are multiple lookups assigned to the cells, which for my current project is not acceptable.
Am looking for a quick solution, (with no interaction required) something that would convert the cells to the format required either before the formula is assigned and overwritten by values or just after. Currently I'm stumped.
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 a formula which extracts a number from a text string.
The text string is in A2 - the formula which extracts the number is in B2
However - when I copy the data in B2 and paste values to another sheet / cell - the forumla is as text - and I get the little green corner symbol and I have to convert to number.
Some VBA code - that will automatically convert my extracted "number" - to an actual number in number format.
Here how to convert Number to Text..Like 100 means One Hundred.For this any formula is there? i want to change Full A Column numbers to Text..Number To Text.jpg
