Complicated (to me!) find and replace

[keri]

Hi,

I am trying to find in a cell on a worksheet which will contain a whole
number and the letter P

eg. 8P, or 2P

I then want to remove the P so the cell reads 8 (or whatever the number
may be) and put the P in another column on the same row.

For example;

Column A of range "carrange1" will contain values like

1
2
3
4
5
6P
7
8
9

However the P could accompany any number, more than one number or no
numbers. In the example above i would like the first column to end up
just showing whole numbers 1 - 9 and the second column to show a P on
row 6.

Is there any way I can solve this?

 

[David Biddulph]

Data/ Text to Columns/ Fixed Width
Separate after the first digit.

 

[keri]

Sorry for being simple but what is the code to do this? I really don't
know which code to use.

Thanks.

 

[keri]

Also the number may be 2 digits. And i need to do this within a block
of code as opposed to manually through menus

 

[L. Howard Kittle]

This might work for you. Enter this in the next column and pull down.
Should return a "P" if there is one and nothing if not.

=IF(ISERROR(FIND("P",B1)),"","P")

Select the column of P's and Copy > Paste special > Values > OK in the same
column.
Select the original column of numbers and P's and Edit > Find what > ~P >
replace with nothing > Replace all. Notice the ~ (tilde) in front of the P.

HTH
Regards,
Howard

 

[keri]

Sorry I am really looking for a way to do this with code. The reason I
need to do this in code is because I will be sorting these "P"'s in
the middle of a huge amount of coded importing and formatting. It's not
something that the user can sort out manually as the rest of the
formatting depends on this being done (automatically).

So sorry for being picky.

I was wondering if I could do an If statement to evaluate if it was an
integer? If not put a P in the next column, if it is do nothing. Would
this work? And if so how do I start writing it?

 

[L. Howard Kittle]

I'll give it some thought using code.
Have to be away for a few hours, however.

Howard

 

[L. Howard Kittle]

I was wondering if I could do an If statement to evaluate if it was an
integer? If not put a P in the next column, if it is do nothing. Would
this work? And if so how do I start writing it?

Did you try this IF statement?

=IF(ISERROR(FIND("P",B1)),"","P")

This evaluates B1 for a P, and if True, returns a P. If no P in B1 then it
returns nothing.
End result is there is a P in the next column if B1 has a P, or nothing in
the next column if B1 does not have a P, which you say is what you want.

Regards,
Howard

 

[L. Howard Kittle]

I was wondering if I could do an If statement to evaluate if it was an

integer? If not put a P in the next column, if it is do nothing. Would
this work? And if so how do I start writing it?

Did you try this IF statement?

=IF(ISERROR(FIND("P",B1)),"","P")

This evaluates B1 for a P, and if True, returns a P. If no P in B1 then it
returns nothing.
End result is there is a P in the next column if B1 has a P, or nothing in
the next column if B1 does not have a P, which you say is what you want.

Regards,
Howard