Search/Replace question.

[plasma]

not sure if I can explain this correctly, but I'll give it a shot..


I have data in a cell with with either end in a "A", "B", "C", "D" or "E" for example

A
1 GA08150A

(or it could be GA08150B, GA08150C, GA08150D or GA08150E)

the GA will always stay the same, the 08150 will change daily and I really don't need to monitor those.

so what I want to do is have a search formula (located in a different cell) that will look for the A", "B", "C", "D" or "E", and replace the entire contents of the cell (ie. GA08150A) with just 1 letter. bascially I want to delete the GA08150 part, leaving only the last letter.

now I know how to do basic searche formulas, but for some reason i cannot figure out how to create the correct formula in one cell that will replace the text in another cell.

hope this makes some sense..

any ideas?

 

[MSAccessRookie]

not sure if I can explain this correctly, but I'll give it a shot..


I have data in a cell with with either end in a "A", "B", "C", "D" or "E" for example

A
1 GA08150A

(or it could be GA08150B, GA08150C, GA08150D or GA08150E)

the GA will always stay the same, the 08150 will change daily and I really don't need to monitor those.

so what I want to do is have a search formula (located in a different cell) that will look for the A", "B", "C", "D" or "E", and replace the entire contents of the cell (ie. GA08150A) with just 1 letter. bascially I want to delete the GA08150 part, leaving only the last letter.

now I know how to do basic searche formulas, but for some reason i cannot figure out how to create the correct formula in one cell that will replace the text in another cell.

hope this makes some sense..

any ideas?

I am not an Excel Expert, but IF The cell is Always structured Exactly the same, then I would think a formula using the RIGHT({CellID},1) function should give you what you want

 

[chergh]

I'm not aware of any excel functions that will do what you want and you can't use a user defined function from the worksheet as UDF's called from a worksheet cannot change another cells value.

So unless there is an excel function that I'm not aware off, and there are a lot of functions I'm not aware/familiar with, your would have to use VBA to do this.

 

[chergh]

You could try something like in an adjacent column and copy it across:

=IF(NOT(ISNUMBER(--RIGHT(D88,1))), RIGHT(D88,1),"")

 

[Alc]

I think the 'Search' function will work.
Start the search at the third character and you'll avoid hitting the "A" in "GA".
You can use it inside a nested 'IF' statement, to cover each letter in turn.

 

[plasma]

thx for the replys

all i can figure out is:

=IF(ISNUMBER(SEARCH("a",E3,3)),"A","")

For some reason when i try and next searches
ie. =IF(SEARCH("A",E3,"A"),(SEARCH("B",E3,2)))


I'm getting errors.

how would I make that so it will search for the A or B and then return which it finds?

 

[MSAccessRookie]

I did a simple test of what I first suggested (modified to return Upper Case Only), with the following results:

Value of A(n)     UPPER(RIGHT(A(n),1))
 
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150A          A[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150B          B[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150C          C[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150D          D[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150E          E[/FONT][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150a          A[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150b          B[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150c          C[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150d          D[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150e          E[/FONT][/FONT][/COLOR]

I am not sure how to update your field, but as you can see, the value that will be used appears to be correct.

 

[MSAccessRookie]

thx for the replys

all i can figure out is:

=IF(ISNUMBER(SEARCH("a",E3,3)),"A","")

For some reason when i try and next searches
ie. =IF(SEARCH("A",E3,"A"),(SEARCH("B",E3,2)))


I'm getting errors.

how would I make that so it will search for the A or B and then return which it finds?

I am not sure where this is going, but the following works (I think)

=IF(ISERROR(SEARCH("A",A4,1)), SEARCH("B",A4,1), SEARCH("A",A4,1))

 

[plasma]

I did a simple test of what I first suggested (modified to return Upper Case Only), with the following results:

Value of A(n)     UPPER(RIGHT(A(n),1))
 
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150A          A[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150B          B[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150C          C[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150D          D[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150E          E[/FONT][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150a          A[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150b          B[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150c          C[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150d          D[/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][FONT=Courier New]GA08150e          E[/FONT][/FONT][/COLOR]

I am not sure how to update your field, but as you can see, the value that will be used appears to be correct.

thank you, that does work perfectly. I guess I was trying to work this out in a search funtion, which I would still like to do. Some of the other data I have may require searching in the middle of the test string instead of the end. I was hoping to get that figured out so it could cover everything i was working on.

So from what i've read, there does not seem to be a way to replace the GA08xxxA with just A in the same cell? The GA08xxxA will be placed in a cell based from a macro i wrote. i was hoping that once the macro placed text in the cell, the formula would then convert it to just the A B C D, etc. I'm assuming it probably can be done better in VB, but I really don't know enough to even go that route.

thx again for all the help..

 

[MSAccessRookie]

So from what i've read, there does not seem to be a way to replace the GA08xxxA with just A in the same cell?

The only thing that I can say for sure, is that I have no idea how you could do that. Perhaps someone else has an idea. Good luck!