Access/Excel: Create multiple Lines/Column out of one mulit-line Column?Hallo everyone!
I spent a lot of time today searching for a response to my question but
I had unfortunately no success.
The problem is the following:
I have got an Access-table called Employee with an ID and one
corresponding Field/Column called comment. These comments are stupidly
multi-line (separated by a Return).
It's my aim to have access (he he, wordplay) to these two fields in
Excel whereas the field shouldn't be longer that 255 characters.
I considered three solutions:
The table could be changed after being exported to excel in the
following two ways:
- Multiple Comment Column...
find and replace hyperlink?Hi, I want to globally find and replace the contents of a hyperlink with a
new hyperlink. For example, I want this
Jul.xls#'Title Page'!A1
to be replaced with
Aug.xls#'Title Page'!A1
I can't seem to find a way do it. Is there a way?
Thank you,
Mike
Take a look at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
me wrote:
>
> Hi, I want to g...
Incrementing Numbers ?Hello,
I have a column with, e.g., two numbers in it.
Say a 2 and the next one down is a 4.
How do I select one or both so that I can simply just drag down and have
a long column with:
2
4
6
8
10
etc.
And, if there are already a lot of numbers in the column, must one
delete them first for the scheme to work ?
I tried a few methods, but just can't seem to get it to work.
Also, couldn't find anything in the HELP menu for this.
Thanks,
Bob
Select both cells then drag the bottom right corner down.
The cursor turns into a small black cross when you hover over the bottom right corner ...
Replace word in sentence function...I have the following function which replaces a word in a sentence with
"....."
=REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....")
Where the word is E2 and the sentence is I2.
At the moment, if the word is "work" and the sentence is "I worked at
home" I get the sentence "I .....ed at home".
Any ideas how I could capture any remaining letters at the end of the
word and get the result "I ..... at home"?
I'd greatly appreciate your help.
Thanks in advance,
Chris
can you upload example, would be easier to view
--
Message posted from h...
how do I set my tab to stop at column N and rtn to AI have an invoice created in excel that I would like to be able to tab over
no further than the end of the invoice(column N) and then return to column A.
Howeer, the tab continues past the column and I don't know how to lock it.
TL,
You can hide all the columns past column N, and the tab will stop at column.
If youve been tabbing, from column A for example, and have arrived in column
N, the Enter key will return you to column A, one row down. Otherwise, you
can use the Home key to get back to column A, then the Enter key do go down
one row.
If that isn't good enough, you can...
Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XPI dread asking this in case someone descends on me like a ton of bricks and
says the answer is in the posts already! - but i I have never used the PP 97
which came with the MS Office 97 Pro Edit I have installed on my computer
which uses Win XP with Ser Pack 3.
I am about to embark on using PowerPoint for the FIRST time and wonder
whether I should, before anything else, acquire a more up to date version
first - and one that will work problem free with my XP - which version should
I go for and will it, on loading, overwrite and replace the Powpoint I
already have and still operat...
Search and replace problemHi,
I have an Excel worksheets where in several cell's a symbol was added to
mark the cell's "to review".
The symbol used was "(*)".
I have tried now to delete all the entries with a "search and replace", but
Excel interpretes the asterisk as a DOS-symbol and deletes everythng between
brackets.
Is there a way to avoid that or to let Excel know that I only want to delete
the specific characters.
TIA
Melissa
It's because it is a wildcard, you can replace it by preceding it with a
tilde ~ which tell Excel to find an asterisk
in find what p...
Serial number in QueriesI am just wondering how to make the serial number through query, and it's not
related with any data or table
Means once I run query there is data will be show and I want to be serial
number shows in the first of each raw.
Ken
Create a table that has the fields matching your query, plus an AutoNumber.
Turn it into an Append query, and append the data to the table. The
AutoNumber field gives you the sequence.
If you need to programmatically reset the seed of the AutoNumber after you
delete the data, see:
http://allenbrowne.com/func-ADOX.html#ResetSeed
--
Allen Browne - Microsof...
MS Money 2004 & large Neg NumbersI installed the Trial this morning and loaded a converted
Quicken 2003 file. It worked fine for a bit. Then all of a
sudden, my home screen and Cash flow projected etc.
screens show extremely LARGE negative balances (in the
zillions) ;-) I cannot find any entry for these large
negative numbers. Anyone seen this? Is there a setting I
have mistakenly changed?
thanks
Doug
In microsoft.public.money, ItsDouglas wrote:
>I installed the Trial this morning and loaded a converted
>Quicken 2003 file. It worked fine for a bit. Then all of a
>sudden, my home screen and Cash flow pr...
How do I combine multiple columns into just 1 column?In my excel file, the address is split into 4 columns. I want to combine the
4 columns into 1.
In a fifth column, use the concanenate function or amphisand to combine the
information. Then drag down to copy for each row.
=CONCATENATE(A1,B1,C1,D1)
or
=A1&B1&C1&D1
HTH
"scosus" wrote:
> In my excel file, the address is split into 4 columns. I want to combine the
> 4 columns into 1.
...
Hiding / Displaying columnsOpened spreadsheet that has numbered tabs above row 1 that show / hide
columns. Also shows + and - signs showing the related columns. Just
wondering how to create this in a spreadsheet. Thanks...
Select the columns (or rows) that you want to apply it to. Then
Data>Group and Outline>Group.
UnGroup to undo the operation
Regards
Rowan
majesticaussies wrote:
> Opened spreadsheet that has numbered tabs above row 1 that show / hide
> columns. Also shows + and - signs showing the related columns. Just
> wondering how to create this in a spreadsheet. Thanks...
...
replace a negative result with a zero?I have my formula entered but need it to return a zero if the result is
negative. Any help is greatly appreciated.
--
Pam
Well, what is the formula that you are using?
You could try this:
=3DMAX(your_formula,0)
to replace your_formula.
Hope this helps.
Pete
On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote:
> I have my formula entered but need it to return a zero if the result is
> negative. =A0Any help is greatly appreciated.
> --
> Pam
Hi,
=if(your formula<0,0,your formula)
"Pam2277" wrote:
> ...
Selective Replace in Search and ReplaceI need to search and replace in a large document. I don't want to replace the
entire search term/expression just part of it. In this case I am searching
for a paragraph return followed by any letter (not digit) and then want to
change that to paragraph/tab but keep the letter. The search looks like this
^p^$
but naturally if replace is
^p^t
I lose the first letter of the text string which I don't want
How can I construct either search and replace so that
^p
Alexander
turns into
^p
^tAlexander
and not
^p
^tlexander
Thanks in a...
optimum method to print rows as columnslooking to convert each spreadsheet row to print as a column per page
--
Concord
If you've got more than one or two rows to deal with, the optimum is probably
the macro below. You'll need to change the names of the two worksheets
involved, and the "EmptySheet" referred to may even have to be added to your
workbook if all the sheets in it are currently being used for something.
To put the code to work: Start by making a copy of your workbook to test
things with. Then open that copy and press [Alt]+[F11] and choose
Insert-->Module and then copy and paste...
Lookup based on criteria in 2 columnsHi,
I am trying to use a vlookup or other function to return the value in the
amount column based on the location and date. Here is a sample of my data:
Location Date Amount
101 9/15/8 10
101 9/16/8 20
101 9/17/8 15
102 9/15/8 50
102 9/16/8 75
102 9/17/8 67
For example if I wanted to return the amount for location 102 on 9/15/8,
what formula would I use? I tried using variations of vlookups but had no
luck.
Thanks,
=SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50)
--
Regards,
Peo Sjo...
Format ColumnI have a column with inmate id numbers in it. I imported them from a
text based program. I made a custom formatting for the column because
all the id numbers begin with zero. IE 00112356 or 01555666 etc...
The format I used was 00000000 under custom.
The problem now is that I want to import them into access but access
doesn't see the zero at the beginning. How can I make it show the
literal number including the zeros? I tried adding the '01222555
before the number, but I would have to manually append 2000 records.
Is there a faster way?
Morph.
Hi
you could use a helper column...
Replace wildcards with a spaceI have a text field with all values containing an asterisk. The asterisk can
be located anywhere within the contents. I am trying to replace the asterisk
with a space. I will then use TRIM to remove any spaces at the beginning of
the text.
I have been unable to get an update query to work. When my criteria is like
"~[*}", Access says that 0 rows will be updated. When the criteria is [*],
I am asked to enter a parameter value.
Any assistance will be greatly appreciated.
Thanks - bkh
Barbara wrote:
>I have a text field with all values containing an aste...
change column names to lettersNormally column headers have names using letters and row
header has names using numerals. In my excel numerals are
used to define both columns & rows headers.
Normally you have A1 cell, and I have R1C1.
How can I change to normal headers, so that Columns header
would consist of letters, and rows header consist of
numerals?
Thank you very much.
Tools>Options>General, uncheck R1C1 Reference style
--
Kind Regards,
Niek Otten
Microsoft MVP - Excel
"Raimundas Dundulis" <anonymous@discussions.microsoft.com> wrote in message
news:01e601c3a7b9$657d6190$a301280a@phx....
Replace more characters using replaceHi
I have number of characters in a string, that I like to replace with
"nothing". I use a code like this:
Sub test()
Dim a As String
a = "Jan+& Per"
a = Replace(a, " ", "")
a = Replace(a, "&", "")
a = Replace(a, "+", "")
MsgBox a
End Sub
As I have about 15 characters, that all should be replaced with
nothing, I am looking ofr another way to it. Something like
Sub test()
Dim a As String
a = "Jan+& Per"
a = Replace(a, {" ",...
Excess columnsI have set up my worksheet, but have a tremendous number of columns left
unused. I have tried to delete them, but they don't go away. How can I get
rid of them?
Karen, you can not, but you can hide them ,select them and format columns
hide
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Karen" <Karen@discussions.microsoft.com> wrote in message
news:594ED2CD-74A9-4356-BEC7-787D45029470@microsoft.com...
>I...
Numbering for columnHi, Good Day!
I seldom used microsoft excel but sometimes i do used it for some simple
calculation.
When I just opened an excel spreadsheet, the numbering of column is in
number format(1, 2, 3...) and not in alphabet format(A, B, C...)
May I know how can i reset it back to alphabet?
Thanks in advanced.
rgds,
Phoebe
Tools|Options|General|Uncheck R1C1 Reference Style
(this is picked up from the first workbook you open in that session. So make
sure you save your workbook after the change--and make a note of how you fixed
it. It can happen again if the first workbook opened has R1C1 refe...
Inventory item numbersThere are quantities for existing item numbers. What is the best method to
move these balances to new item numbers:
1) Buy item number modifier from Professional Tools
or
2) Use decrease adjustments to reduce the existing balances to zero, then
post increase adjustments to the new item numbers.
Jackie,
Is there any reason that you would want to keep the history of what happened
under the old number vs. the new one? If so, then go with option 2.
Will the new item be set up differently than the old one? If so, most
likely you'll want option 2 (this will be true for most 'major...
Numbering Add-In #2I have a purchase order template I created with Excel 97
which includes an automatic counter field to give each PO
its unique ID number. When I try to open this template in
XP, I am told that I need to install the numbering add-in
into the Library. However, I cannot find this add-in
amongst those available in Excel. I have had no luck
trying to source it on the Microsoft website, find
reference to this specific add-in in Offline or Online
help etc. WHERE IS IT????
This add-in is no longer part of Office:
http://support.microsoft.com/default.aspx?scid=kb;en-us;288118&Product=xlw
...
Replacing old Windows XP and Outlook Express with new Windows 7How do I export the contents and settings out of Outlook Express and import
the accounts, settings and emails into Windows Live Mail on another machine?
For messages:
Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx
must be included). Place this on the Desktop or other location on the
machine using WLMail. Open WLMail and: File | Import | Messages |
Microsoft Outlook Express 6 and point to where you saved it.
For Addresses:
Open the Address Book in OE and File | Export | Address Book (wab) and
save it to the Desktop. Copy to flash or CD. Place this on the...
Group header page numberingHello All Access Guru out there
Would like to have some guide / code / example on how to reset a page
numbering to 1 of each group?
ie Total pages = 6
Group Pages
Site 1 1 / 1
Site 2 1 / 1
Site 3 1 / 2
Site 3 2 / 2
Site 4 1 / 1
Site 5 1 / 1
thaks in advance
Hi,
Try this (But it's in french)
http://officesystemaccess.seneque.net/ex_pagination_groupe.htm
TopJB
PWYS a �crit :
> Hello All Access Guru out there
>
> Would like to have some guide / code / example on how to reset a page
> numbering to 1 of each group?...