Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide certain characters in a cell
I have copied a cell from one workbook to another. When it copies over it is
accurate and shows an * at the beginning and end (which is how it shows in the first workbook). In the second workbook, I don't want these * to show. Is there way that I can automatically hide this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide certain characters in a cell
Two ways:
if you want to remove the * then use something like:=MID(A1,2,LEN(A1)-2) if you just want to "hide" the *, then select the * in the formula bar and format that character to have the same font color as the background. -- Gary's Student "Julia" wrote: I have copied a cell from one workbook to another. When it copies over it is accurate and shows an * at the beginning and end (which is how it shows in the first workbook). In the second workbook, I don't want these * to show. Is there way that I can automatically hide this. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide certain characters in a cell
Thanks for the assistance...not sure this is working for what I need
though...currently I have 2 workbooks. In workbook # 2, I have a formula to copy a cell from workbook # 1. In workbook # 1 it is actually a barcode but in workbook # 2 it is just test. It all copies over without a problem but when it copies over, it shows the * at the beginning and end of the test (in workbook # 2). The * must be there on the bar-code so I can't remove it there. When it copies to workbook # 2, I need the * to not show. This applies to multiple cells on a document so I don't want to have to change the colour to match the background every time...I am trying to figure a way to just have it either not show or be removed automatically when copied over. Any ideas? "Gary''s Student" wrote: Two ways: if you want to remove the * then use something like:=MID(A1,2,LEN(A1)-2) if you just want to "hide" the *, then select the * in the formula bar and format that character to have the same font color as the background. -- Gary's Student "Julia" wrote: I have copied a cell from one workbook to another. When it copies over it is accurate and shows an * at the beginning and end (which is how it shows in the first workbook). In the second workbook, I don't want these * to show. Is there way that I can automatically hide this. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide certain characters in a cell
If you have no "internal" *, then we can make them all vanish as follows:
Edit Replace in the Find what field enter: ~* leave the Replace with field blank then Replace all -- Gary's Student "Julia" wrote: Thanks for the assistance...not sure this is working for what I need though...currently I have 2 workbooks. In workbook # 2, I have a formula to copy a cell from workbook # 1. In workbook # 1 it is actually a barcode but in workbook # 2 it is just test. It all copies over without a problem but when it copies over, it shows the * at the beginning and end of the test (in workbook # 2). The * must be there on the bar-code so I can't remove it there. When it copies to workbook # 2, I need the * to not show. This applies to multiple cells on a document so I don't want to have to change the colour to match the background every time...I am trying to figure a way to just have it either not show or be removed automatically when copied over. Any ideas? "Gary''s Student" wrote: Two ways: if you want to remove the * then use something like:=MID(A1,2,LEN(A1)-2) if you just want to "hide" the *, then select the * in the formula bar and format that character to have the same font color as the background. -- Gary's Student "Julia" wrote: I have copied a cell from one workbook to another. When it copies over it is accurate and shows an * at the beginning and end (which is how it shows in the first workbook). In the second workbook, I don't want these * to show. Is there way that I can automatically hide this. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide certain characters in a cell
This macro will remove all the * characters from selected
cells..........whether they be entire column, entire row, or a non-contiguious selection using the CTRL button....... Sub RemoveStars() 'Hold down CTRL and select cells you wish to remove the * 'then run this macro Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub Vaya con Dios, Chuck, CABGx3 "Julia" wrote in message ... I have copied a cell from one workbook to another. When it copies over it is accurate and shows an * at the beginning and end (which is how it shows in the first workbook). In the second workbook, I don't want these * to show. Is there way that I can automatically hide this. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide certain characters in a cell
Another way to try...
Paste this macro behind the sheet in the second workbook, then cut/paste the range of data into it. It will remove the *'s on cells as they are entered or copied over. Thanks Gary"s Student for the formula. Regards... Christopher Tri Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo EndMacro Application.EnableEvents = False For Each cell In Target If cell.Value < "" Then If cell.HasFormula = False Then If Left(cell.Value, 1) = "*" Then cell.Value = Mid(cell.Value, 2, Len(cell.Value) - 2) End If End If End If Next cell Application.EnableEvents = True Exit Sub EndMacro: Application.EnableEvents = True End Sub "Julia" wrote: I have copied a cell from one workbook to another. When it copies over it is accurate and shows an * at the beginning and end (which is how it shows in the first workbook). In the second workbook, I don't want these * to show. Is there way that I can automatically hide this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Number of characters in a cell (one row, merged columns) | Excel Discussion (Misc queries) | |||
if cell starts with characters formula | Excel Discussion (Misc queries) |