extract numbers from text string
I have a column of cells with information in some of the cells only. the
information is text with numbers (the text can be of varying length), for
example: C3 could contain: 1 avml 12 chml 1 special occasion.
There could be as many as 12 variations in the string of text.
I need to extract all the information into other cells, seperating the
numbers from the text so H3 = 1 I3 = avml, etc.
How do I do this with visual basic?
We need a little more information... what do you want to happen when
multiple words occur without an intervening number? For example, how did you
want the words "...Sum query (single value) to appear in a text box
I am have a SUM query (which works okay). I want to display the value
of that query in a read-only text box on a form. However, when I set
Control Source for that text box to
it does not work for me. I do not see any other way to do this. Can
someone give me some advise on how to do this?
- Paul Schrum
one way is in the form's load event, put:
Me.yourTextbox = nz(DLookup("summedfield", "qry_theSumQuery"),0)
<email@example.com> wrote in message
news:firstname.lastname@example.org...Problems when saving worksheet into tab-delimited text file
I save a spreadsheet into a tab-delimited text file for later import into an
One column contains text strings in which "comma values" can be found.
Once save to the text file, those records that have commas in the Excel
cells now contain double quotes (".....") at the beginning and end of the
As a result, during the data import into Access (and later comparison), the
records are considered new and add records to tables.
During testing procedures, I realized that replacing the comma with a
semicolon would not add the double quotes when savin...Import external data-text files but placed in the next column rather than next empty row
I want to import text files in to one worksheet. The text files are sent
daily and all the same format. However the text file names change buy an
Is it possible to create a macro that imports these text files,
ignoring their exact filename but from the same source directory into
one worksheet. Can you use wild cards for the name e.g. "********.txt"?
At the moment I can manually do this, by selecting the cell below the
last record then using the process "Get external Data" and following
the wizard which works fine.
As soon as I try to create a m...from "date" to "text"
I have cells formatted as date (e.g. 11/9/03) and I'd like to change
the format to text. But when I do it, the values change as well (e.g.
"36472"). What can I do to avoid this?
You can't. Dates, to Excel, are the number of days since Jan 1, 1900. That's
the what you are seeing when you try to change the format to text.
You need to tell us why you want it as text. You have lots of other choices
of formats available.
If you absolutely have to have text, you will need to create a helper cell,
and use a formula like:
=text(a1,"m/d...Using Excel with Access
Is it possible to have excel update automaticlly if I'm entering data
into an offline Access Report. I'd like to be able to enter data into
access and have it update my excel report at the same time. Is this
Possible? If so How? Thanks
petevang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25034
View this thread: http://www.excelforum.com/showthread.php?threadid=400501
...My Office application cannot start because Office is already in use...?
Upon opening all of my microsoft word and powerpoint documents (but not
excell for some reason) I recieve a message that informs me that "an
office program is being used by ... Your installation exceeds the
number of installations permitted by the license agreement." Clicking
out of this message shuts down the document. My confussion stems from
the fact that I have not reinstalled or altered my program in any way.
The only change in my computer, when I began recieving this message,
was that I was connnected to the internet (which I usually am not).
Why could this be happening???
&...failed to restore full backup using ntbackup
I tryed on my lab inviroment backup my xp (C:) partitian using
ntbackup, then I changed ntldr file, the restore didn't work here is the
Backup of "C:", Restored to"C: "
Backup set #1 on media #1
Backup description: "Set created 12/7/2009 at 1:13 PM"
Restore started on 12/7/2009 at 1:34 PM.
Warning: File AUTOEXEC.BAT was skipped
Warning: File boot.ini was skipped
Warning: File CONFIG.SYS was skipped
Warning: File IO.SYS was skipped
Warning: File MSDOS.SYS was skipped
Warning: File nag...Can a form's query use dbSeeChanges to get new autoincremented key?
When a form was bound to a jet table I could get the new autoincrement
pkey value in the before insert event. Is there a way to do with with a
slq server 2005 odbc linked tables? Something like the dbSeeChanges
param that one can use with recordset inserts?
No, you can't because of the case of an ODBC linked table to a SQL-Server,
the new autoincremented value will be defined only after the creation of the
new record. Access/JET seeds the new value before creating the record - so
you can access it even if the record doesn't exist yet in the table - but
SQL-Server doesn't w...Outlook automation: Problem with RTF-Text in body
I hope, this is the right forum!
I'm using Outlook 2003 SP3 automation for sending emails out of an
SQLWindows program written with TD5.1, OS is WinXP prof.
Everything works fine with plain text, but when I'm trying to send RTF
formatted text all the format characters can be seen in the mail, although
Outlook displays the right message format: RTF.
I set the BodyFormat property of the Mail item to olFormatRichText and
placed the text string in the Body property ( using HTMLBody doesn't work
The text is well RTF-formatted, I can open it with different edito...using RANK
I know i can rank a column of numbers from largerst to smallest.
Can I use rank for smallest to largest? Any help appreciated.
Help has a pretty good explanation, look for the order, 0 or omitted is
descending and 1 is ascending
"Skip" <email@example.com> wrote in message
>I know i can rank a column of numbers from largerst to smallest.
> Can I use rank for smallest to largest? Any help appreciated.
...Formula needed: count if cell CONTAINS certain text
What formula do I need to count the cells that CONTAIN a certain text
A1 = John
A2 = Pieter
A3 = William
count cells that CONTAIN "i": 2 (A2 and A3)
What (set of) formula should I use? Excel Help doesn't give me the
On 7 Dec 2005 04:28:36 -0800, "Wim" <firstname.lastname@example.org> wrote:
>What formula do I need to count the cells that CONTAIN a certain text
>A1 = John
>A2 = Pieter
>A3 = William
>count cells that CONTAIN "i": 2 (A2 and A3)
>What (set of) formula...an imported text field from Excel displays as an exponential number in Access
I'm trying to import an Excel worksheet into an Access table, and one of the
text fields in Excel contains numbers stored as text. In Excel, one of
these values displays as:
But after I import the worksheet into Access, it displays as:
It appears that Excel is sending this data to Access as a number, rather
than as text. I realize that as far as Excel is concerned I'm storing a
number as text, and while it's all well and good that Excel is trying to be
so helpful, I would like it to treat the data as text. That's why I
formatted...How to get TEXT() to display minutes?
If I code
I get the month number. How can I get TEXT() to return the minutes?
Does =MINUTE(B2) do what you want?
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Square Peg" <SquarePeg@Round.Hole> wrote in message
> If I code
> I get the month number. How can I get TEXT() to return the minutes?
On Sun, 28 Sep 2008 09:24:57 +0100, "Bob Phillips"
>Does ...Populating text field based on Picklist selection
Hi, there is probably an easy solution to this and I'm missing something very
I have a picklist which, when a value is selected, I then need to populate a
seperate text field with a corresponding value.
Eg; Picklist contains "City 1", "City 2", "City 3", etc.
I now need an OnChange to enable to following formula ...
If 'Picklist field' = "City 1" then 'text field" displays "State 1"
If 'Picklist field' = "City 2" then 'text field" displays "State 2"
and so on ....
I'm ...using dlookup in a query
I'm creating a Bill of Materials using a one-to-many relationship table
between InternalPN and ExternalPN. So the query sometimes returns more than
one record when an InternalPN is specified, because there are multiple
External PN's. I would like to alert the user to this in my query by
substituting the word "Multiple" for the ExternalPN.
I can find multiples easily enough with the Query Wizard & so have created a
separate query called QryPNMultiple.
I'm trying to refer to that query in a dlookup statement in my BOM query.
I've tried this:
Expr1:...Using "do Visual Basic" from AppleScript to access Office 2004
I am trying to automate word,excel and ppt via
Java-Cocoa-AppleScript-VB. The Java application exists already. The
objective here is to have the *same* interface access all three apps
.... this seems impossible given the inconsistent level of method
exposure MS have provided.
Using all AppleScript fails because PPT has a very limited set of
objects that can be accessed from AS. For this reason I decided to
investigate using VB to do the work (yes, even though the mechanism to
return values is broken) and use the 'do Visual Basic' command to send
it to the app.
It does not look lik...Using countif with a range of dates
Column Z contains multiple dates ranging over 10 years. I am trying to count
the number of cells in Column A that are between two dates (e.g., 01/01/07 -
I tried using
however, the count if not correct. The result is 3,172 via the above formula
which is wrong. Using a filter, I know the correct result is 56, but I
cannot correct my formula to reflect.
W...Viewing text 2003 vs. 2007
I have a 2003 worksheet with many lines of wrapped text. When I select a
cell I can read all the text in the formula bar. But I sent it to someone
with 2007 and they claim only the first line of text is visible.
Is there an option in 2007 that needs to be selected to read all text?
ask the person to double click on the cell or at the top, widden the area
where you see the text. If the text had a control break (alt+enter) then it
may have stacked the data and they are looking at only the first line
"Matt S" wrote:
> I have a 2003 worksheet with ma...Skipping Text Entries
One of my charts contains "N/A" entries. Right now they are showing as zeros
on the chart. I went to OPTIONS/CHART and the chose the LEAVE GAPS choice but
they are still showing as zeros. Is there any way to skip that week if the
entry is non-numeric? Thanks.
have your text entries return the #NA error, such as:
The #NA error is ignored by charts.
*Remember to click "yes" if this post helped you!*
> One of my charts contains "N/A" entries. Right now they are showing as zeros
>...unable to download templates
i am unable to download the templates or anything why can some one help me
A post by Ken Macksey..............
I don't know if this will work for you, but it worked for me.
With IE open.
1. Click tools
2. Click internet options
3. Click privacy tab
4. In the web sites area, click edit
5. In the address of web site textbox, paste
8. Click apply
9.Exit ie and restart system
After restarting your computer, try going to the template gallery and it
End Ken post.................................
...Find certain "Text" in a specific coloum, add corres. values
I have two coloums. One has text and the other has Values.
I need excel to search through a whole coloum, and summerize the results.
My coloums look like as follows
The results should look like
How can I achieve this?
This is quite expandable using sumproduct for a criteria in B and matching
in C and then sum C.
"Arum Khan" <Arum Khan@discussions.microsoft.com> wrote in message
> Hi...How to fix error "Unable to load client print control" ?
We get an error when clicking the printer icon on a report generated through
SQL Reporting Services via GP.
The error message is "Unable to load client print control"
The report data does output correctly in IE, however, when we click on the
printer icon to print the report we get the above error.
Previously we never used to get this error. We have made sure the ActiveX
components have been installed on all workstations that allow this printing
Does anyone have any ideas how to resolve this error in SQL Reporting
http://support.microso...how to use vba in excel
I would like to work with an example sample to enter vba int excel
Have you tried using the macro recorder.
If you're new to macros, you may want to read David McRitchie's intro
Microsoft MVP Excel
"Barkeroo" <Barkeroo@discussions.microsoft.com> wrote in message
>I would like to work with an example sample to enter vba int excel
...Addition on "text" cells
I wonder if this is possible, cell A1 contains the text "P0001", cell
A2 contains "P0021", A3 contains "P0041" and so on until "P0581". The
values are separated by 20 units.
If the cell is numeric, I could just make the formula on A2 as "+A1+20"
and copy it down. This will give me the result from cell A2 21, 41,
Is there a way to make a formula or something so that I can have
"P0001", then "P0021" and so on, on a single column.