Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my
McLaughlin, Victor, (i.e, comma) W
McLaughlin, Victor.(i.e., period) W
Is there an extract and replace formula or method of som sort (in
excel or access) that will allow me to pull the first comma from the
right and replace it with a period.
Thanks for any suggestions!
Select the cells you want to change and run this tiny macro:
For Each r In Selection
v = StrReverse(r.Value)
r.Value = StrReverse(Replace(v, ",", ".", 1, 1))
wi...Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in
this particular sheet it should be cell DA197). I have used both the methods
described in the Knowledge Base article (deleting rows and columns and
re-saving; and the Excess Format Cleaner add-in).
Deleting the rows and columns does not work; using the Excess Format Cleaner
does not work either and it then also hides the rows from 198 to 65536 - but
does not do the same for the columns.
I have checked that there is no protection on the worksheet.
Has anyone else come across this problem and if so can you please ...Add Word and change format
1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8", etc...Now
I want add a "Z" in front the codes. To be "ZPK0021, ZUQ05P8". What's the
faster way in case I got thousand of codes?
2) In my colomn B is such code as "18-521-65, 18-81-84, 18-1112-65" and etc.
Now I would like to make it to be standard to 4 digit for the middle number
to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the same senario as above,
I got more than thousand of such codes... What's the faster way?
The first could be done wi...Conditional formating of Charts
I have a scatter chart and need to conditionally format points and
associated labels for each point. The condition is not related to the data
series to which the chart is linked.
Let me know if there is any specific VBA code for doing the same. You can
reach me at firstname.lastname@example.org
There are some non-VBA suggestions here:
To change some points of a series or some series in a chart based on a known
criterion, you can adapt one of the macros in this post:
I am creating a template which requires the user enter a time (not
present time, flight times), for example the user will be listing a
number of flight arrivals and departures and what I want to do is have
the user simply enter something like 804p and have the cell display it
as 8:04 pm. I have looked at some custom formats and nothing seems to
be able to do this. Any help would be appreciated.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
http:...tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when
this phone is away from the office. This is PDA phone.
On 14 Dec 2005 11:38:11 -0800, "Filip - beginner"
>How can I use exchange to send tasks to cell phone. I want to do when
>this phone is away from the office. This is PDA phone.
If you're using a Smartphone you can sync tasks already with
"Mark Arnold [MVP]" <email@example.com> wrote:
>On 14 Dec 2005 11:38:11 -0800, "Filip - beginner"
><fwitkowski@gmail...Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug
because there are also a lot of rows.
Is there anyway to change display so it replaces the column name e.g.
CustomerName is a defined name range for BT1204
Find & Replace
Find what: BT1204
Replace with: CustomerName
> I have a # of fairly long/complex cell functions that get hard to debug
> because there are also a lot of rows.
> Is t...Calendar in Cell Validation
I want to implement a cell validation such that when the user attempts
to input a date, a "list" box-like functionality pops up that has a
calendar and the user may then choose the date by picking with the
How would I implement this?
Try the following Web site.
This site's author covers this in a tutorial, but also provides downloads.
<firstname.lastname@example.org> wrote in message
>I want to implement a cell validation such that when the user attempts
i use microsoft outlook calendar for all our daily
appointments. i do not want to have the highlight
when i ask the assistant, i do as it says, but i cannot
click the automatice formatting (it is gray and will not
how do I get automatic formatting so that I can get rid
of the highlighting and add color
...extract info from cell, then count
I have a 2-part question:
(i) I have 1000's of e-mail addresses but want to extract the countr
from the e-mail i.e. email@example.com, where de (Germany) is needed. How d
I isolate the ".de" (and others eg .fr, .edu, .com etc etc)
(ii) Having done the above, I then need to do a count. Rather than us
COUNTIF and include the code for every country in the world, is ther
any other way of counting? I guess a Pivot table?
Message posted from http://www.ExcelForum.com
=MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LE...Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and
some bank cells and I want it add them and put the total into the second last
row (7 in this case). The last row contains a multiplier (3) which when
applied to the total results in 21. Below is the table.
| | | 3 |
| | | |
| | | 4 |
| | | 7 |
| | 3 |21|
How can I achieve this in VBA (under Word 2003 and 2007) remembering that
the user can add rows to the table and the last column can contain blank
Thanks in advance for any assistance,
Sub ScratchMaco(...How do I put Excel data into a US map format?
I want to feed Excel data about population and trends into a map format
instead of a bar graph or pie chart. Is there a plug-in or some such thing
that I can use that works with Excel? Ultimately, I want to have each state
depicted by a color code for a range of population or an amount of certain
I am using Excel 2002 in a Windows XP environment.
...Conditional Formatting #N/A to show white
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<br> I have several formulas that show #N/A
until a value is present
<br> I tried Conditional Formatting to turn that cell white until
a value is inserted
<br>but I don't know what I am doing..</html>
This is a multi-part message in MIME format.
Is it possible to format a portion of a text string within a cell (as opposed
to the entire cell). For example, I would like to format the word 'gift' in
red font anywhere it a appears in range C2:C417 but only that word, not the
Not with conditional formatting.
But you could change the actual format for that word (or group of characters)...
Saved from a previous post (or two!):
If you want to change the color of just the characters, you need VBA in all
You want a macro????
Option Compare Text
Application.ScreenUpdating ...VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include
a number with 5 digits then a city name then a region name, such as "11090
CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this
field changed as following : "F-11090", then copying "CARCASSONNE" into the
City field which is empty (column G). The city name is always starting just
one space character after the postcode, same thing for the region name, it
always starts one space character after the city name. The region has to be
...Finding Cells that Total a Value
I need some assistance in solving a problem. I have a spreadsheet with
over five hundred lines of transactions. The sum of these transactions
are creating a balance on the account. Is there any formula/macro that
will help me find the transactions creating the balance? The sum of the
account should be zero. To clarify, if we owe client money, there
would be a transaction setting up that postive balance then a payment
on the account taking it back to zero. There could be multiple
transactions and then one net payment. Or we could be due to receive.
So at the end of the day, th...go to cell with date equaling TODAY()
Hello! I have a spreadsheet with January 01, 2006 thru December 31, 2006 in
ascending order in column A, each date in a different cell (A1, A2, A3,
etc.). I don't enter data into this sheet everyday..in fact, months could go
by before having to enter an occurance for say, March 31. Is there a way to
have excel, upon opening the spreadsheet, advance the cursor to the cell with
that day's date in it?
Thank you all for your help!
Using function Date rather than Today()
Works for me.
Private Sub Workbook_Open()
Dim r As Long
Dim T As Long
T = Date
r = Application.Matc...ExMerge stops working until I backup information store
I use ExMerge to archive mailboxes of people who leave the company and
about 80 percent of the time if works fine. But every now and then I get
a failure with the log saying it was unable to open the message store.
Despite having the correct permissions, it will continue to fail until I
back up the mailbox store and then it works fine again.
Can anyone explain to me what the backup is doing that then allows
ExMerge to work.
i never heard of anything like that before...are these accounts disabled?
are you re-enabling them?
Susan Conkey [MVP]
"Jan" <user...Date formatting problem #5
I've tried everything I can think of with this one - help!!
I'm reading dates from 2 cells, storing them in variables, comparing them
and then storing the resulting date in a different cell.
Code currently is:
If stStoredDate<> stDate then
Activecell.Offset(0,1).Value = stStoredDate
My problem is stStoredDate = 04/09/05 (4 Sep 05 as I'm in the UK), stDate is
the same, but the value in the cell is 09/04/05 (9 Apr 05). Both cells have
identical formatting. If I change the formatting to 'general', stStoredDate=
38599, but t...Formatting cell for phone numbers
I am trying to format a cell with phone numbers so that it will look
like the following:
(937) 129-9876 fax
The word 'fax' is more specifically a label, not necessarily that
word. I would like to have carriage returns between the numbers with
the possibility of several numbers. This may be confusing and/or too
complicated, but I'd like to give it a try. If anyone as suggestions
or possible solutions, I would appreciate hearing them.
>I am trying to format a cell with phone numbers so that ...Cell format options are truncated
When you click on any cell and click to format it, tabs do not display
Are you editing the cell at the time?
You may only see the Font tab if that's the case.
Make sure you're not in edit mode before you click on Format|Cells...
> When you click on any cell and click to format it, tabs do not display
...wmp format is too small
when i play a netflix video cd with wmp in full screen the video itself is
about 1/2 the size of the full screen wmp iow there is a black border on all
i have tried just about every option i can find in wmp but no change.
any ideas of suggestions?
Does it show up correctly outside of fullscreen mode? Have you checked
for updated video card drivers?
Speaking for myself only.
See http://zachd.com/pss/pss.html for some helpful WMP info.
This posting is provided "AS IS" with no warranties, and confers no rights.
On 5/15/2010 11:46 PM, d...Word documents are re-formatted to an undesired font on opening
I have saved documents with an Arial based style but on re-opening the styles
have Times New Roman and different font sizes.
How did you change the styles?
Microsoft Word MVP
"KeithCC" <KeithCC@discussions.microsoft.com> wrote in message
>I have saved documents with an Arial based style but on re-opening the
> have Times New Roman and different font sizes.
...cannot change part of a merged cell
I merged cells and come time to paste from the clipboard into a merged cell,
I get the error it can't be done. I can hit F2 and paste. It's an annoying
step and users who are not quick on the draw with Excel would be stuck.
Is there a way to stop the error and succeed with a paste?
don't use office clipboard use Ctrl + C & Ctrl +V
On Apr 7, 5:46=A0am, "shank" <sh...@tampabay.rr.com> wrote:
> Annoying problem...
> I merged cells and come time to paste from the clipboard into a merged cel=
> I get the error it can...selectively deleting cells
I would like to selectively delete alternate rows on an excel worksheet containing a long list of data. i.e. deleting rows/cells A3,A5,A7,A9,A11....A30573 etc. How do i do this quickly without having to use CTRL + select for each specific cell/row?
It isn't clear what you mean by "selectively" delete the rows. If
you just want to delete every other row, use code like
Dim RowNdx As Long
Application.ScreenUpdating = False
For RowNdx = 30573 To 3 Step -2
Application.ScreenUpdating = True