InStr function

[Guest]

Hi all. I am new to Excel VBA, Here's my problem.
I have a string in a cell for example: "this is my string : category"
What I need to be able to do is find the position of the ":" and return it
as an integer. I understand you can use the InStr function to do this. But....
How do I pass the result onto the spreadsheet itself?
Further to that, how do I use this same function for 50 more rows below?
I have tried using in the cell formula bar something like
"=MyFunctionName(CurrentCell)"
But all I keep getting in the cell is "#NAME!". Any ideas? Thank You.

Ash.

 

[Niek Otten]

Hi Ash,

You don't need VBA for that:

=FIND(":",A1)

returns 19

If you insist on using VBA, don't use a Sheet Module, use a standard module (in the VB editor: Insert>Module)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi all. I am new to Excel VBA, Here's my problem.
| I have a string in a cell for example: "this is my string : category"
| What I need to be able to do is find the position of the ":" and return it
| as an integer. I understand you can use the InStr function to do this. But....
| How do I pass the result onto the spreadsheet itself?
| Further to that, how do I use this same function for 50 more rows below?
| I have tried using in the cell formula bar something like
| "=MyFunctionName(CurrentCell)"
| But all I keep getting in the cell is "#NAME!". Any ideas? Thank You.
|
| Ash.

 

[Guest]

Ah excellent thanks for that, I'll give it a try and let you know how I get on!

 

[Guest]

Hello again,
That worked brilliant. The only problem is, in some of the cells, a ":" is
not present, hence at the moment the result returned is "#VALUE!". How can I
prevent this? A simple IF statement of some sort?
The purpose of all this is to split a string into two different cells i.e.
Cell A1 = "Split Categories : Internal Transfer"
Needs to be converted into:
Cell A2 = "Split Categories :"
Cell A3 = "Internal Transfer"
I know to use the LEFT(A1,X) Function, but what about for the string AFTER
the ":", referenced Cell A3 above.
Hope thats not too confusing, thanks.
Ash.

 

[Niek Otten]

Hi Ash,

=IF(ISERROR(FIND(":",A1)),"",FIND(":",A1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello again,
| That worked brilliant. The only problem is, in some of the cells, a ":" is
| not present, hence at the moment the result returned is "#VALUE!". How can I
| prevent this? A simple IF statement of some sort?
| The purpose of all this is to split a string into two different cells i.e.
| Cell A1 = "Split Categories : Internal Transfer"
| Needs to be converted into:
| Cell A2 = "Split Categories :"
| Cell A3 = "Internal Transfer"
| I know to use the LEFT(A1,X) Function, but what about for the string AFTER
| the ":", referenced Cell A3 above.
| Hope thats not too confusing, thanks.
| Ash.

 

[Niek Otten]

=RIGHT(A1,LEN(A1)-FIND(":",A1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello again,
| That worked brilliant. The only problem is, in some of the cells, a ":" is
| not present, hence at the moment the result returned is "#VALUE!". How can I
| prevent this? A simple IF statement of some sort?
| The purpose of all this is to split a string into two different cells i.e.
| Cell A1 = "Split Categories : Internal Transfer"
| Needs to be converted into:
| Cell A2 = "Split Categories :"
| Cell A3 = "Internal Transfer"
| I know to use the LEFT(A1,X) Function, but what about for the string AFTER
| the ":", referenced Cell A3 above.
| Hope thats not too confusing, thanks.
| Ash.

 

[Guest]

Excellent. Many thanks Niek. Is there an address I can contact you on if I
have any further probs or should I just post here?

Thanks again,
Ash.

 

[Niek Otten]

Post here, Ash,

There are many more people answering questions here so you don't have to wait for me to be awake

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Excellent. Many thanks Niek. Is there an address I can contact you on if I
| have any further probs or should I just post here?
|
| Thanks again,
| Ash.

 

[Guest]

Hi again, no problem this time just a simple question.
I am an advanced user with Access 2000, been programming VBA and designing
accounts applications for 2 yrs now.
Just wondering though, I want to improve my Excel skills and maybe create
some useful apps in Excel. But whatever I think of, I always drift back to
Access because it always seems to be easier and quicker.
What would you typically use Excel for? I don't mean simple accounts
spreadsheets, i'm talking more in terms of full applications with features
aplenty.
Bit confused on this one. It just seems that anything Excel can do, Access
can do better...Correct me if i'm wrong.

 

[Niek Otten]

Complex calculations with lots of dependencies are best done in Excel, because you don't have to keep track of all the
dependencies yourself.
It's also a matter of taste. Some programmers insist that calculations can easily be done in COBOL. Poor guys.....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi again, no problem this time just a simple question.
| I am an advanced user with Access 2000, been programming VBA and designing
| accounts applications for 2 yrs now.
| Just wondering though, I want to improve my Excel skills and maybe create
| some useful apps in Excel. But whatever I think of, I always drift back to
| Access because it always seems to be easier and quicker.
| What would you typically use Excel for? I don't mean simple accounts
| spreadsheets, i'm talking more in terms of full applications with features
| aplenty.
| Bit confused on this one. It just seems that anything Excel can do, Access
| can do better...Correct me if i'm wrong.