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.
I have an Excel file with several worksheets and each of those worksheets has over 600 rows (a list of employees) and above or under 30 columns. Most of cells have trailing spaces in them and they vary in length. For example:
B5: "Jacobson " C5: "jacobson@jacobson.org " (Seems like it's not allowing me to post long gaps between the last letters and the "
I'm aware of the TRIM function and know that I could at least get a column or a few copied with the spaces removed. However, it would just take so much time. What would be the most efficient way of getting rid of all the trailing spaces in all the cells in all the columns in all the worksheets?
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 am trying to get rid of the spaces at the beginning of text that is the result of a download from a reporting software package. I am using the Trim function but it does not work. It seems that it has something to do with the formatting. The first set of data of the download looks as follows: SalesSales BreakdownSolenaceousCucurbitsLargeSeedIf I overtype the text in exactly the same way (thus keeping the spaces at the beginning), the trim function works. But this is not a practical solution.
The type of the cells is "2". I have tried copying the data as values to a different workbook but this does not help either.
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.
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
I am having trouble trying to figure out how to insert blank cells:
I need to insert cells, in columns B through H, equal to the number of returned IDs in column B.
Please take a look at the spreadsheet I attached.
Here is the code I am using to separate the IDs in column B so that I get one ID in each A cell. When I do this it separates them great, but then I have to manually go in and insert cells down so that the rest of the rows match up with the right ID.
Sub Macro1() Dim fromCol As String Dim toCol As String Dim fromRow As String Dim toRow As String
I have a lists of e-mail address (up to 15 rows long) in adjacent columns.
At times one or more addresses need to be removed. After removal I need the list to 'bunch up' so that the space caused by the now blank cell is removed.
At the moment I am doing this with vba as follows:
Sub Clear_Blanks()
With Range("e13:e27") . AutoFilter .AutoFilter Field:=1, Criteria1:="<>" .Copy Range("e35") .AutoFilter .ClearContents End With Range("e35:e49").Cut Range("e13")
How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop.
This is my first post on this forum and I am an Excel novice. I have a worksheet with lots of blank cells and I need to shift all the cells with data all the way to the left (to column A). I cannot get the sort or filter functions to do this for me, and I've never used macros but it appears they might be useful for my problem. I'd like to find an automatic process to move the data left rather than drag and drop hundreds of times. I'm attaching the complete xls file for your viewing.
I am looking to remove all blanks from a table I have and move all the data left. Right now the data is by date, and some dates are blank for some players (it is a basketball sheet) and filled for others, but I want to see it by game. So I have what is in the first table below, and I want it to look like what is in the second.
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 was thinking something like... but this doesnt get rid of the blanks... (this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.
I need to do a lookup on these cells but for some reason they all appear to have some kind of space characters in each cell which you can see if you double click on them. I have tried trims, text to columns, find replaces and none of these seem to work
I have a huge list of these numbers / codes and they all have the weird space kind of characters at the end
Can anyone give me a solution on how to remove them so my lookup will work?
I have cells in a sheet containing varying numbers of items separated by semicolons. I want to convert these to columns using text to columns. The trouble is there are spaces after each semicolon, and I don't know how to get rid of them. I believe this may be compounded by the fact that the cells containing varying numbers of items. When I convert to columns I don't want each cell in the new columns to start with spaces. I'm attaching an example of what I mean (sanitized with fast food joint names).
Spaces after entries keep messing up my VLOOKUPS. I get a lot of data from other people and when they have entered the information in some of the entries have a space after the word which mess up the results of my vlookups. Is there an easy way of going through and removing spaces after a word? Not all the words have spaces after them.
In my spreadsheet, column A, I have a list is part #'s. I have found at the end of my part #'s there are several spaces in each cell. I need to remove these from the cells so that my formulas will be easier to use. Is there a way to do this? The part #'s very in length and format. They would look like this "100020x0 ".
I have a list of product numbers that I would need to convert into correct format. The logic is that the maximum number of characters is 14, but the there are 6 different formats. In my list there are spaces added and I would need to delete the needless spaces.
For XXX XXX XXX correct format is XXXXXXXXX (no empty spaces)
I have a column with 11 digit numbers formatted as text (Column C) and it seems like there's three spaces after the number (In Sheet A). I am trying to do a vlookup comparing Sheet A to Sheet B. It's not working and not sure if it's the extra spacing in the cell of Sheet A that's causing the Vlookup not to work.
I would like to compare both lists to see what UPC's are on both sheets. Any ideas how I can do this?
In Sheet A, Column C contains UPC (11 digits) 07845968952
I have some data that is seperated by spaces. I need to split the data so each is in a seperate cell and then remove the spaces. I can do this if there is only one space inbetween the data, but that is not always the case. I attach a workbook with examples.
I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
I have come up with this to Trim all of the data from rows 2:30 removing any trailing spaces after the last word in each cell. The macro takes a couple of minutes to run have I got something wrong that is making it run slowly or does the Trim process just take longer?
Sub TRIM_RANGE() Dim myRange As Range Dim myRow As Range Sheets("CAMPAIGNS_2007").Select Set myRange = Range("2:30") If myRange Is Nothing Then Exit Sub Application. ScreenUpdating = False myRange.Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False For Each myRow In myRange.Columns If Application. CountA(myRow) > 0 Then myRow.TextToColumns Destination:=myRow(1), _ DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) End If Next myRow Application.ScreenUpdating = True End Sub
I have blank spaces between data that I wanted to fill up with the same info as a group. the problem is it is not in a fix format.
For example 1 ___A_______B________C________D 1_aaaa____data a____data a____data a 2_________data a____data a____data a 3_________data a____data a____Total 4_________data b____data b____data b 5_bbbb____data b____data b____data b 6_________data b____data b____Total
I need to fill A1 & A3 with aaaa and A4 & A6 with bbbb
Is there a way I can do that? the data is seperated only by the "Total"
I have a worksheet where the user input data and i have to make a macro that exports certain cell from the worksheet to a text file without any separating symbol, just like “CONCATENATE and EXPORT”. This is not hard to do, i’ve done it already. But the difficult part is here. My customer wants this macro to be interactive with the user and with an AS400 server that takes the textfile and reads it to find the value of certain variables, something like the opposite process of CONCATENATE. The hard part for me is that if any of the cells concatenated is blank i have to fill the text file with blank spaces.
If the concatenated cells are: |one| two| three| tour| five|
the exported text to the text file will be:
onetwothreefourfive
But if one of the cells is blank:..................
