ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

3 Q & A posts tagged with REPLACE

Mon
Nov 14
2011

Is it possible to replace a char in a string of chars in java?

I want to replace a char in a string of chars by another char .Is it possible?if it is , how to do that? Thanks in advance


Answer

Here is how
http://www.w3schools.com/jsref/jsref_rep…

VBAXLMan

Monday 11/14/2011 2:33:15 AM
Sun
Nov 13
2011

How to assign a random number as an id to a marked text in a cell in excel?

What code can I use so that I can assign a random number as a the ID for a text in a cell. For example cell A1 has these sentences:
{I have (three) dogs and (two) cats}. My pets always fight. {My (dog) is afraid of my (cat) and they are both afraid of (mice)}.
I need an excel macro code so that it will assign a random number as an ID for the items enclosed in the parenthesis. I need this macro to assign a random number ID one at a time. In other words what I want the macro to do is to:
1. Search for curly braces in the cell
2. Get all the items enclosed in the curly braces that are enclosed in parenthesis
3. Assign a random number as an ID to the items in the parenthesis without touching the other text
4. Then move on to the next text enclosed in curly braces and continue until there are no more curly braces in the cell

So what the macro will do is that it will look at the contents of the selected cell. Then look for curly braces then look for words that are enclosed in parenthesis and then assign a random number as an ID for each then move on to the next curly braces.

The curly braces and the parenthesis may be changed to any marker.

All help is greatly appreciated.


Answer

The main command you might need is:
Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Doing it repeatedly is the trick that you will be using to replace all options once found, note that "Replace" works only if that text is found

And then put it inside a loop to check for them, like this

X1=1
Max1 = range("A1" ).currentregion.rows.count
do until x1>max1
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(cat)", "(" & Int( Rand()*100) & ") cats")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(mouse)", "(" & Int( Rand()*100) & ") mice")
x1=x1+1
loop


let me know if you need more help

Sunday 11/13/2011 6:47:20 PM
Wed
Apr 8
2009

How do I find and list all values in Excel?

I have a workbook with many sheets listing all of the schools my org interacts with. Each sheet lists 1-5 email addresses, depending on how many contacts we have.

I want to have a summary sheet that lists all of the email addresses so it's easy to copy-paste them to send out newsletter.

I did a FIND/REPLACE for "@" and it found all of the email addresses, but it won't let me copy paste them from the search results.

I've also been trying to do reserach on how to do this with a formula, macro, or VB but haven't come across anything.

Thanks!


Answer

Here is a simple macro that will help you doing that

http://www.file1.net/project.asp?id=x16f…

Enjoy it,, it will copy all rows from all other sheets than the current one into it for manipulating easy

VBAXLMan

Wednesday 4/8/2009 1:16:26 AM

Tags

History