Some code below which I have inherited, basically its looping through one column of data and removing the text at the beginning before the hyphen eg. '123 - data text' to 'data text'.
HTML Code:
'Remove everything before the hyphen in the activity column
Dim SearchStr
Dim CharOffset As Variant
Range("c7").Select
[Code] ......
I'm compiling several old worksheets into a single database for a research study. The worksheets contain patient data. Some of the worksheets have the patient's last name, first name, and middle initial entered all in one cell like this: Smith,John R. Compounding the problem is the fact that sometimes the name is entered with a space between the comma and the first name, sometimes not.
I have formulas to break the name out into three separate columns "lName, "fName", "midInit". However, if the name in the original cell has a space between the coma and the first name, then the “fname” column will contain a blank space in front of the name. This is problem because patients names can appear in the database more than once. Some patients are in the database several times.
If patient “John R. Smith”, for example, is entered in the database as”
“Smith” “John” “R “
and also as
“Smith” “<space>John” “R”
then the database won’t recognize them as the same name when I search for John Smith’s data. Right?
If so, then I need a way to eliminate the empty spaces in front of the first names. Like I said, some have empty spaces and some don’t. I could do this by hand, but there are over 1000 entries in these worksheets.
In many cells, a dollar amount (variable lengths) was pulled into the cell. I am trying to use the RIGHT function to remove it but my formula isn't working. A cell example and formula example are below.
P31 = "$693.00 Research and analyze case law regarding Motion in limine reply"
=RIGHT(P31,LEN(P31)-LEFT(P31,FIND(" ",P31)))
This returns an error, no matter how much I think (wish) it should do what I would like it to.
I have some data in the form of text w/ 8 letters. I'd like to insert a hyphen after the third character. Is this possible using a formula to populate an entire field? Example:
Current format: ABC01234
Desired format: ABC-01234
What is the formula to cover multiple different text that could come up? I could have an answer that is NA, Yes and also a blank cell and if I could I would like to have it set up as follows:
NA = -
Blank Cell = 0
Yes = 1
I am setting up a new chart of accounts, with the format xxx-yyyy, where xxx is a three digit department number, and yyyy is the expense account. I am building a spreadsheet to populate the new chart of accounts to be uploaded into our accounting software. I would like to have a cell that takes the xxx from one cell, adds a hyphen, and combines it with the yyyy value. The result would be xxx-yyyy format.
View 3 Replies View RelatedAttached is a sample sheet with nine columns. Column D includes a name, but in many cases it is preceeded by: 'TO', 'BY', or 'OF' and a space which I need to remove (if they exist), leaving just the name. Please note that sometimes just the name exists so nothing needs to be done. I believe that in a jet sql querie I can use something like:
View 7 Replies View RelatedI have a column which contains a series of alphanumeric characters some start with the alpha characters and others end with them.
As an example ABS1234, 1234ABS, 12k34AB needs to be ABS1234, ABS1234, AB12k34. The number of alpha characters at the end will be a minimum of 2 and a maximum of 3. Is there some way that I can move the Alpha characters, at the end, so that they are always at the beginning of the string?
I have lost the formula for this looked everywhere, I have Content that looks like this e.g 1dg, 15gh, 8fj, 20lk,
I reqiure the formula to split the number into another cell(numbers will be no more than 2 digits), I already have the formula for the letters.
I want to add blank spaces to a cell with text
My cells have up to 14 spaces
If a cell as 1 or 2 characters -I want to add 6 spaces in front of text.
If a cell as 3 or 4 characters-I want to add 5 spaces in front of text.
If a cell as 5 or 6 characters-I want to add 4 spaces in front of text.
If a cell as 7 or 8 characters-I want to add 3 spaces in front of text.
If a cell as 9 or 10 characters-I want to add 2 spaces in front of text.
If a cell as 11 or 12 characters-I want to add 1 spaces in front of text.
I have a simple code that should insert the text at the beginning of the text file (the text file already has some text in it). When i use the following code, it just appends the line to the end of the text. I need it to append it to the start of the text file.
Code:
Private Sub CommandButton1_Click()
Dim file As String
lastrow = Range("D65536").End(xlUp).Row - 4
file = "C:Users11126923Desktop est.txt"
Open file For Append As #1
f = "hi test3"
Print #1, f
Close
End Sub
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
IDEAL COLUMN HEADINGS:
NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |
etc etc etc
Having difficulty with a VLOOKUP that uses a text lookup field beginning with 0. For all the other numbers formatted as text I use this function:
[Code].....
For the one that begins with a 0 I have to use this function:
[Code] .....
This is a problem for me, because I need to be able to copy the top function to all of my sheets without having to edit the ones where the lookup field begins with 0. I could alter my VBA to specifically search for those cases and adjust, but that seems unneccesary.
More info - The beginning column of the Table Array (i.e. the lookup field I'm searching for) is a function of another cell; both cells are formatted as text. Here is the function:
[Code] ....
I have to put a single quote in front of the lookup field, so that "064" is not displayed as "64" even if the cell is formatted as text. I have another instance where I am looking up "'791" and it works fine with the first VLOOKUP above. Even if I remove the single quote this will work. However, once there is a leading zero the function no longer works and I must use the second VLOOKUP listed above.
I have a long list of values in column F of my worksheet. These values appear in random rows for example: cell F3 is 27, cell F9 is 7, cell F13 is 27, cell F20 is 9 ... The data is not evenly spaced any specific number of rows apart, but there are spaces (rows which are blank in column F).
I would like to put all of these values in column G but without any blank cells, and keep the order the same as well. So using the above example, Id like a way to make cell G1 read 27, G2 read 7, G3 read 27, G4 read 9.
Column F could be a very long column im not sure exactly how long as the data is being put in periodically. Some sort of formula that I could just drag down that would read the values from column F and put them in column G with no places would be ideal.
I have many excel wordlist, which are being converted from other file formats. Now there are sometimes useless spaces before entries in the cells. There are also some times commas and other not needed signs at the beginning of a cell in a column.
I need a macro, which removes spaces, symbols, letters, numbers or combination of them from the beginning of all cells in a column. The symbols should be chosen before the macro starts its work.
For example:
Before running the first macro:
Column A
,Word1
,,Word2
Word3
,Word4
After running the macro the beginning should be cleaned from chosen symbols, spaces or signes. (in our example if a cell begins with , then the commas should be deleted).
Column A
Word1
Word2
Word3
Word4
I have used this code below to split a large excel file into multiple sheets from matching column data, but now I need to split it by a partial match (set number of characters from the beginning) from beginning of the column data.
For Example:
[Code]....
So with the code provided below using column 3 I would get 10 different sheets since none of the data in the column is identical. I want to modify the code (or come up with new code) so I can set the number of characters to compare from the beginning of the data in the set column and split into sheets based on that. So if I set it to the first 4 characters in column 3 I would receive only 5 sheets sheets: Safe, Fail, Dont, Poop, & 21-4.
What are the modifications or new code needed for this? I have searched for a bit with no luck, just keep finding code to check the full cell data for matches in a set column like this code I have:
SPLIT DATA FROM ONE SHEET TO MULTIPLE SHEETS
[Code] ......
I am trying to create a list of customer credits for my store. I have the person's name, the date of their credit, and the amount of the credit. This list will eventually be substantial and I would like to know how I can set it up so that when I open the worksheet I can enter new names from the top of the list and have the rest of the list shift downwards. This would stop me from having to scroll to the very bottom to add new info every time.
I would like to have it set up this way so that eventually I could put a search query at the top of the page (i'm thinking of rigging something up using vlookup) so that when my employees want to access the data base they can easily search a name and have the relevant info brought up, or can easily type in new store credits.
I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.
There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.
The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)
I am trying to remove a middle initial from a text string however not all of the cells have middle initials. In column B I would like to return the first and last name. If needed I can have the first name in column B and the last in column C then combine them.
Example
John A Smith John Smith
John Smith John Smith
I'm trying to come up with a single formula to create a single column list from a table with blanks.
a
b
c
d
e
f
g
To
a
b
c
d
e
f
g
I know I've done this before but having trouble visualizing today.
1, Is it wise to have a continuous looping file like this as on some computers when this file is run it sounds like the computer is going to take off?!? (having said this it will only be ran on one computer that will never be used for anything else while the program is running)
Well - all thats happening is that the Excel workbook is hogging essential system resources - i.e. memory and CPU time and extra continuous CPU load is causing the machine to heat up which switches on the Fan.... be thankful the Fan is working
I didnt do a code walkthrough to see if there was a more efficient way of doing it, but if the machine isnt being used by anthing else, and it is in a well ventilated area (Air Con?) then I wouldnt see this as being a big problem. Just make sure the fan doesnt break down or you might have some sparks.
2, Could this be done in a powerpoint presentation. e.g have an excel spreadsheet on each slide and automatically update the links every x mins. You could then just set the slide show to continuous loop.
Well, possibly yes... the idea is good, but as far as I know you would have to rewrite the code to work in PPT. That is - you cant just drop an excel sheet with code onto a PPT slide and expect it (The Excel code) to work... you would need to code each slide again. I think... Not sure how easy that would be as I know little about PPT.
I have a macro that seems to get bogged down during a replace function because sometimes there is no need to replace. I figure I need to loop this and cannot figure out how to do it.
I have the code below and need to know how to loop this ....
In column A, I have many entries such as AA-1, AA-2, BB-1, BB-2, AA-3, etc.
How do you count the number of entries for only those beginning with "AA" or beginning with "BB"?
I have 4 columns of data, Col A id for pipes, Col B upstream_node_id, Col C downstream_node_id and Col D connected assets. I want to create Col E with the cumulative count of upstream connected assets.
Data looks like this :
A B C D E
Pipe ID U/S Node ID D/S Node ID Nr Connected Assets Nr U/S Connected Assets
78057358 74888997 1026722856 2 39
78057352 74888992 74888997 4 37
78057346 75107198 74888992 6 33
78531653 74888757 75107198 6 27
78057340 74888987 74888757 2 11
78057046 74888762 74888757 6 10
78057334 74888982 74888987 9 9
78057040 74888767 74888762 4 4
See how the D/S Node ID can appear more than once in Col C.
I have added Col E manually to demonstrate the output I am looking for.
I am no expert but I think that this would be something like a nested loop, but the number of nested loops can't be determined before hand ??
I have written out what I think I need to do, but don't really know how to code it :
i. For each Pipe_ID (Col A) get U/S Node ID (Col B) and add to "selection".
ii. For each U/S Node ID (Col B), find all matching U/S Node ID (Col C), and add to selection and get the U/S Node ID (Col B) for those.
iii. Populate Col E with sum of Col D for "selection".
iv. Repeat Until U/S Node ID (Col B) can't be found in U/S Node ID (Col C).
How to get the table to align correctly.
What I'm trying to do: While there's a value in Col A starting with A2, I need to then go to the corresponding cell in Column O, starting with O2. Then as long as there's text in the next cell in the same row keep track of those cells so that in the end I can append all text in the adjacent cells to the text in the Col O[X] cell. Then repeat as long as there's text in the next row cell of Col A. I have the routine to append the text, I just need help with the looping.
IE:
If I start with the following:
Col A Col O Col P Col Q
row 1 ----- ----- ----- -----
row 2 1 Text here new text
row 3 2 More text more text 2 more text 3
row 4 (no text in any of this row.......................................)
After running macro this would become
Col A Col O Col P Col Q
row 1 ----- ----- ----- -----
row 2 1 Text here..............................
i would like to take the first letter of a cell and add 1000 to it.
At the minute, ive only been able to take away the first letter which isnt what i want to do
so far i had
=RIGHT(B1,LEN(B1)-1)
I need to remove values from column A that are present in column C without modifying the order in column B. To be more explicit below is an example:
Column A---------Column B-------Column C
5230400----------81,50----------1660000
3275500----------78,00----------6245700
2856300----------47,50----------5230400
1879800----------62,50----------2497900
2124300----------99,00----------1879800
2497900----------65,00
4826900----------77,50
2124200----------34,00
6245700----------61,50
3324400----------86,00
1660000----------53,00
Column A represents 600 materials.
Column B represents 600 prices. (different for each material)
Column C represents 107 materials that are present in column A and have to be removed along with their prices in column B.
Now i want to remove the materials that are in column A and C along with their prices, so i won't damage the order in the file.Meaning if i delete a row in A and shift the other materials up or down, and in the same time column B remains unchanged, the remaining materials will have different prices...and that's not good.
I am attempting to Organize some data; however, I have run into a problem. I will paste a sample of the data that I have and explain the situation....
View 9 Replies View RelatedI have a column of data which has numbers and units (small example below).
I need to remove the text (units) and at the same time multiply the number by a value which is based on what the text is:
-999.9uA needs to become -999.9*10^-6
-98.40mA needs to become -98.40*10^-3
