Complicated (to me!) find and replace
 

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?

Complicated (to me!) find and replace
 

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

"keri" wrote in message
ups.com...
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?

Complicated (to me!) find and replace
 

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

Thanks.

Complicated (to me!) find and replace
 

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

Complicated (to me!) find and replace
 

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" wrote in message
ups.com...
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?

Complicated (to me!) find and replace
 

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?

Complicated (to me!) find and replace
 

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

Howard

"keri" wrote in message
ups.com...
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?

Complicated (to me!) find and replace
 

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

"keri" wrote in message
ups.com...
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?

Complicated (to me!) find and replace
 

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

"keri" wrote in message
ups.com...
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?