I have a file with thousands of records and on the name field, I have case like this:
1 "John Dough"
2 "John Dough "
As a result, when I pivot the data, I see duplicate lines for the same name. How can I eliminate the extra spaces at the end (row 2 and 3 above)?
I have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;
=============
Part 1: 5x10 Red Cotton candy
Part 2: Yellow 6/18 x7 TTC x11 Picture Frame
=============
My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.
What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.
My question is there something else I can do that is easier and more accurate?
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).
View 2 Replies View RelatedSpaces 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.
View 4 Replies View RelatedI have a column of numbers that are in sets of 3. (123 456 789) I need to remove these spaces and just see 123456789.
View 3 Replies View RelatedIn 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 ".
View 2 Replies View RelatedI 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)
[Code].....
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
In Sheet B, Column E contains UPC (11 digits)
Am i able to remove blank spaces from cells retrospectively?
i.e. i have 1000 cells with names appearing in a cell thus " john smith" i want it to be "john smith".
so just removing the leading blank space only.
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.
View 6 Replies View RelatedI'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 data which has lots of these hyphen "-" how can i get rid of them,
for example -Eq Cash-
i need the result to be
Eq Cash
Also i have lots of front spaces in my data, how can i get rid of those? so for example
Test (there is 2 spaces before T)
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?
I have an Excel 2013 address book that has extra spaces between first names, last names and middle initials all in 1 cell.
Is there an easy way to remove all the spaces between these components?
I have a spreadsheet with Mobile phone numbers in the following format:
+44(0)77 7296 5210
The spreadsheet has 2500 of these phone numbers.
Is there a way to remove the brackets and the +44 to leave:
07772965210
I am using Microsoft Office for Mac 2011
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 a table with 6 columns (with headers A -F) and 10 rows. There are values in each cell. I want to take a value from each of the 6 columns and concatinate them. I would like some code that would generate every possible resulting string and I would like the possiblities set out in one column.
Apart from the fact that it might be tricky enough to produce the code , by my calculations there will be 10 to the power of 6 possibilities and as far as I know there are only 65000 rows in my version of Excel. If they had to be put into 2 or 3 columns I would not mind
I have the below spreadsheet and require to format it depending on what appears in column AE and column N.
example if in AE it says AAA and the figure in column N is >=-1 and Microsoft Excel - Belgium - Jupiler League.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAB3AC3AD3AE3AB4AC4AD4AE4AB5AC5AD5AE5AB6AC6AD6AE6AB7AC7AD7AE7AB8AC8AD8AE8=
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE2DateHomeAwayFTHGFTAGFTRHTHGHTAGHTRHDA51GoalsHDA17GoalsHDA7GoalsWHHWHDWHA51177*303/08/2007MechelenAnderlecht01A00DA-1.02A-2.18A-1.715.53.51.534.54.54.5AAA404/08/2007CharleroiRoeselare11D10H*****H2.00H3.291.623.45-1-1-1HH504/08/2007Club*BruggeBergen21H11DH2.65H2.47H2.001.3647.50.360.360.36HHH604/08/2007DenderGerminal12A00D***************2.883.22.2-1-1-1*
704/08/2007FC*BrusselsWesterlo01A00DA0.76D0.94H1.712.23.22.881.2-1-1ADH804/08/2007GenkCercle*Brugge31H31HH2.31H2.24D1.001.3348.50.330.33-1HHD2007-2008*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How can I put in a formula which will look at the contents of cell A1 and B1 and then if the contents of cell A1 match you put in the contents of cell B1 into B2. I know this sounds confusing so let me explain cell A1's contents are 1 and B2 has the amount of $312.50 so if cell A2 is also a 1 then I need to put in the amount of $312.50 into B2, same would go for B4 since A4 matches the contents of A3 I need to put $625.10 into B4....
View 9 Replies View RelatedI have 4 employees by 52 weeks tabs in a workbook. The first 4 tabs are as follows:
Pat 010810
Dan 010810
Max 010810
Ann 010810
I would like some method (maybe VBA?) that I can rename the subsequent tabs, having the same names but the date with a seven day increment; i.e.the next tab would show Ann 011510, etc.
I don't know how to code, but can get to the VBA page and copy really well.
I need to transfer a large amount data (~2,5 million lines of data) from one spreadsheet to another while making sure the destination file is kept in a certain format.
Spreadsheet "oxno" is the file that needs filling out with data provided to me in spreadsheet "ox"
The value that is the matching criteria will be the ITS account number (Field B in "ox" and field A in "oxno") and the currency (Field B in "oxno" and field G in "ox")
So I want a macro to look in "oxno" and use field the values of field "a" & "b" and search for the them in "ox" in fields "B" & "G". Once it finds a match it should copy the value of field "I" in "ox" and paste into field "n" on "oxno".
The next data to be copied should be what goes into field "O" in "oxno" this data is derived from field "L" or "M" but only when its of numerical value with one non numerical field in it (usually a "/"). Last value that I need copied over is Field "T" on "oxno" this is derived from field "J" or "M".
I realise the potential conflict because two sets of queries will be looking to gather values from field "M" on "ox". That is why I am hoping when we look for value for field "O" in "oxno" we only look for numbers with one non numerical field in it (usually a "/") .
After finishing writing this up, I realise how wordy this has become appologies for that, but hopefully someone will be able to understand what I am looking to achieve and will be able to suggest a solution.
I would like to do a mass paste of this formula: =VLOOKUP(A23,A2:C9, 2)
With the only variable that changes along the way (A24, then A25, etc). But as I paste, (A2:C9), the table array, keeps changing as well, to A3:C10, A4:C11, etc.
How can I prevent this and only have A23 change as I paste?
I have a list of 2000 hyperlinks (http) that I need to download, I'm working on a corporate network so I can't just use a mass download client. Cany anyone give me some guidance creating a macro to save the target of the hyperlink as a file then move on to the next hyperlink until the list is exhausted.
=NORM.DIST(-0.7525,0,1,FALSE)=0.30057
I want to do inverse 0.30057 To get -0.7525 ,NORM.INV(0.30057,0,1) This function returns only cumulative distribution .
I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
I have a workbook with lots of analytic data from my company which I want to share with other people not associated with the company. Obviously I can't share private data so I'm wondering if theres a quick way to modify all the data I have with 'fake numbers' which means I could share it. With fake numbers no privacy rules will be breached etc.
Is there a quick way to randomly change all numbers by +/- 10-20%?
Lets say i have 2 columns A and B as shown below. I want to have a userform that has a list that shows the uniques in column A ie. 1,2,3 and a drop down next to each one that shows my options yes, no, maybe. If the user selects yes for 1 and submits the form i want all 1's in column a to have yes in column B.
A B
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
I am trying to separate a huge group of phone numbers from one cell into individual cells in a column. the numbers are currently separated by a semicolon. Is there an easy way to perform this action?
View 5 Replies View RelatedI have a large worksheet with a unit name column, category, brigade, etc.
Everytime I get the data, I have to make the same changes. Is it possible for me to automate the process so that for example if the name column contains 'CID' or 'AMC MGD' delete those rows, if it contains 'ENG' change the brigade column for that row to '92ENG' etc.?