Remove * character

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
I hope that this is is not too dopey a question.

I receive a sheet of 1000 plus records frequently where in column A each record is part of an English post code with an asterisk at the end like AB1• or KT32*. However many characters there is always a * character.
I would like to be able to remove the * and then add it back after I have finished.
As ever many thanks for any help you can provide.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
To remove them, select col A > Ctrl H > findwhat: ~* > leave replace with blank > OK
To put them back in use
Code:
Sub Addstar()
   With Range("A2", Range("A" & Rows.count).End(xlUp))
      .Value = Evaluate("if({1}," & .Address & "&""*"")")
   End With
End Sub
 
Upvote 0
Make a copy of column A and paste to a different worksheet
In column B of existing sheet use formula "=LEFT(A2,LEN(A2)-1)"
In column C of existing sheet use formula "=RIGHT(A2,1)"
Copy columns B and C and pastespecial as values

Now you can use columns B and C to re-create your original column A or from the backup you copied to as first step.
 
Upvote 0
I would like to be able to remove the * and then add it back after I have finished.
After you have finished what?

Can you explain why you want/need to remove the * in the first place?

And why do you then want/need to add it back 'after you have finished'?

Can you give us some sample data and explain what you are actually trying to do?
 
Upvote 0
Peter I just have a need to do some work on the internal post codes and then reset them back to the format that they arrived in. The example in my post are what I receice
 
Upvote 0
Fluff
Thank you that works a treat I now recall from many years ago the tilde character,
Again my sincere thanks
 
Upvote 0
JackDanice
Likewise thank you for taking an interest and your solution also works a treat

Regards to you all from a rather gloomy wet Southern England
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top