I am trying to remove ’ê characters from my excel sheet, as they are preventing me from using the text to columns function correctly. I have attached an example extract of data, could someone let me know how I can remove these, as the find and replace wizard in excel doesn't allow me to do this?
View 5 RepliesI need to remove/trim in column B the last three characters in each cell so that I am only left with the first 12 characters.
View 3 Replies View RelatedCurrently I've been able to remove the prefixes and suffixes using the replace option as those were consistent across the data set, I'm now left with a series of numbers that look like this:
11.225D2
11.24c1
11.28.r1
11.31.1.2h1
11.36.g1
11.39 A2
11.39B1
11.3c1
In most cases I need to remove the information after the last period. Some of the data has a space at the end, instead of a period and then some have no space or period but have the letter that needs to be dropped. I'm using xl 2011 for MAC.
See the attached file. I regularly receive input files which I have to reformat and upload to a website as a .csv. The website does not like the / between the characters on the Input Data tab. Is there a way to automatically search and remove them and leave the rest of the characters intact? The columns do not always appear in the same order and there could be more or less columns. On the Import Template tab, columns A-I will always be there, so ideally the code should make the changes from the Input Data tab and copy the present columns (J thru whatever) header and data over to the Import Template tab starting in 'J1'. I think that VBA would be the best way to go due to the variable nature of the columns.
View 4 Replies View RelatedI have the following in cell A1:
0113 CDW 3 yr Coterm - Ironport devices
I need to remove 0113 from this cell to read only:
CDW 3 yr Coterm - Ironport devices
Any formula to do this?
I ran a couple of formulas in column E and now it looks like as shown in the attached image.
The cells now contain different characters at end. Sometimes its a couple of commas, hyphens, blank spaces etc.
Is there a formula which can remove the ending characters in case they are not alphabets or numbers?
1. I am trying to remove some unwanted characters in the cells. I have in column A a list of stock counters preceeded by their tickers e.g.
1D42 - CM TEL
1G47 - CSE GLOBAL
Prior to exploring VBA, i was manually doing mid(A1,8,20) to remove those characetrs before the actual name starts but as my list goes on indefinitely, the numbers grow and now i have a few thousand stocks in my portfolio. Also, for some names i cannot use mid(A1,8,20) anymore if the tickers are longer than 5 or 6 characters or are simply shorter.
So now I am trying to see what code will enable me to remove all characters before the actual name i.e one character space right after the Hypen. The hypen only appears ONCE in the cell as the names will not contain them.
2. In addition, after all is removed, what is the code that will be needed to do an automatic Sort of the data corresponding to the stocks (From column A to K) based on alphabetical order?
I am trying to create a CSV file to import all of our workers into a payroll software. Unfortunately the list of workers that I have been given to work with has a first and a last name together in one cell. I need to split them into separate cells so the data ends up in the right field in our software. By importing external data and separating the names with the comma as a delimiter, I have ended up with the first name in one cell and the second in another, which is exactly what I need. However, this process also ends up putting a quotation mark in front of the first names in the cells in column A, and a quotation mark after the second name in the cells in column B.
I remember to removing trailing spaces with a macro once, and I am hoping the same theory can be applied to make two macros - one to remove preceding quotation marks, and one to remove trailing quotation marks. The only trouble is, I know no VBA whatsoever, and don't even know how to make a macro unfortunately. Would someone mind letting me know if what I need is possible, and then - literally step by basic step - explain how to make a macro and provide the code to paste into it?
I'm needing some code that will remove an open quotation mark from the numerical value found in column A. This will be for every row with a numercal value.
example problem: "360015
example answer: 360015
All numbers will be 6 digits
And I've got another issue with numerals in column C. To the left side of the Cell there maybe a Number or Letter. To The Right side of the cell
there will be a single or double digit number. Both will be separted by some space (6-8 spaces). As in the examples below:
(again what you see on each line is in one cell in col. C.
R 59
E 6
0 4
I need the value on the left side of the cell to be moved concenate/merge with the values in column B.
There's will be approx 2000 rows that will need to be checked for this occurrance. Since there's so many rows,
I'd really would like to use VBA and not formulas to accomplish this.
Let us assume x and y are strings. Now let us make x equal to a small sentence (ie. full of words and spaces). I now need to somehow make y equal to x, but with the last word removed. I need to do this in VBA.
View 3 Replies View RelatedI need to remove the letter within the middle of a persons name.
For example, if the person is called 'Michael J Fox' then it needs to be edited to show only 'Michael Fox', if the person is called 'George W Bush' then only 'George Bush' should be displayed etc. However the persons name should remain as is if a middle character initial does not exist.
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"
I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.
My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*
(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.
So for example; if there was a phrase in the list like ....
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 need to know how to remove characters from the date field in Excel.
Example:
9/12/1975 - I need it to read 09121975.
I also need to know how to remove characters from a social security number without removing the zero's in the front.
Example:
012-34-5678 - I need 012345678.
I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:
00 - Preconstruction
01 - General Conditions
I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.
Second thought:
If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
I'm trying to use conditional formatting to highlight cells in a column that have less than 8 characters.
I know the LAN function, but I don't know how to make it work for the conditional formatting.
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So:
Charles Johnson -> Johnscha
John Smith -> Smithjoh
Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
I have a formula in a workbook that keeps giving me #DIV/0! no matter what I do. I am trying to total up a column of numbers. If I enter into B28 the following formula : =SUM(D4*E4)+(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)+(D10*E10)+(D11*E11)+(D12*E12)+(D13*E13)+(D14*E14 )+(D15*E15)+(D16*E16)+(D17*E17)+(D18*E18)+(D19*E19)+(D20*E20)+(D21*E21)+(D22*E22)+(D23*E23)+(D24*E24). it does not give me the #DIV/0! but the result is incorrect.
what I need is a formula that totals rows 4 through 24 but the formula for each row individually would be =(d4/b4*e4) but I am trying to avoid having to add multiple extra columns (& clutter) for each item in each row that I need to calculate. I have attached the workbook so that you can see what I am trying to do.
So I created a custom Add-In, but the add-in isn't removing itself when I uninstall it or when I close the program (this creates an error in saving files, so all files are getting corrupted)
This is all the code I have running the menu bar and I can't figure out the proper closing code
I am trying to organize some meteorological data for a project and I ran into a wall, basically I have 3 columns in one there is the date, in one the hour and in the third one the temperature the issue is that in the hour section i have the hour 12:00 that repeats its self , and this goes for the hole year , pretty much every day i have the hour 12:00 that repeats twice , so select for every Monday , Tuesday etc only certain hours.
View 5 Replies View RelatedI have the following forumla....
=SUM(B11+E11)/F11
in those three cells there is a value of 0
the following message appears in the formula cell - #DIV/0!
is there a way to change my formula so it doesn't display this message and just return 0
how i would remove telephone numbers in a particular spreadsheet which are preceded with 44 which need to be removed and then replaced with a zero.
eg. 441234567890
I have list of data references about 60000 of them but some are duplicated. I have used advanced filted then unique records only. So now i have just the unique records showing now. How do i copy the accounts that is just unique into a new worksheet? I tried copying it but its copying everything. I even tried using paste value but still copying everything?
View 4 Replies View RelatedI have some cells full of user entered information that has returns in it, so that it's multiple lines long. What's the best way to remove returns from a string?
View 3 Replies View RelatedI am Facing is I get this error as #DIV/0! when i use the formula where fields are blank or 0.
The Formula is: -
=IF(E4>=L4,(E4/L4)-100%,(E4/L4)-100%)
What changes can i do in this formula that it calculates exactly as it is doin now but instead of #DIV/0! error it returns the value 0.
It seems like I am asking a question at least once a day. I searched the net and forums and could not find an answer to this question. Thanks to everyone for the help I am making a lot of progress because all of you. Basically, I have a read-only workbook which the user gets a prompt to save as a new workbook for editing purposes. I have a autorun macro so now when the user opens their workbook, it contains my autorun macro. I do not want this. Is there a way to remove modules so that the users copy has no macro information?
View 9 Replies View Relatedi 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 have columns of prices in 2 decimal format that I'd like to manipulate, some prices are whole dollars(no cents & no decimals ) and most are dollars and cents in decimal format-- but to do it successfully, i need the decimal removed. I looked at the "format" function but it doesn't seem to allow for that.
is there any way I can have these prices converted to "cent" format( ie removing the decimal, where applicable), showing them not as "dollars & fractions" but as "cents" i'm sure that would solve the problem for me last time I did it by hand and vowed there had to be an easier way!
I was wondering if anyone had a formula or Macro suggestion to remove rows with no information. I made a pivot table that feeds to another sheet in order to make it more user friendly. The only thing is when there is no information I have formula to returns a zero. I would rather the row be hidden.
View 9 Replies View Related