How to Insert brackets within string

[jaimy]

In my worksheet, I have two columns, Col A for users to enter projects
numbers while Col B for work description and Col C for cost spent. The
project numbers and cost are then collected into pivot table in another
workbook for other calculation.

The problem now is some of the project numbers are like this 1100,
1100(A), 1307(E), 1611(A), 1611(B), 1612(C), 1612(D), 10303(1) and
etc.

So, if the users enter 1100A instead of 1100(A), then I cannot do the
caluculation correctly. If the user enter 1100A mistakenly, Can we do
some code to change it to 1100(A) ? using IF-else maybe or ??

Please advice on how to write the code.

Thanks,
Jaimy

 

[JMay]

Insert a column between your now A & B moving your B to C;
In B3 enter:
=IF(ISERR(OR(SEARCH("(",A3,1)=FALSE,SEARCH(")",A3,1)=FALSE)),"Bad Number-
Re-Enter","OK")
and copy down.
HTH

 

[JMay]

Better... This makes sure the "(" and ")" are within the last three
characters of your input.

=IF(ISERR(OR(SEARCH("(",RIGHT(A3,3),1)=FALSE,SEARCH(")",RIGHT(A3,3),1)=FALSE
)),"Bad Number- Re-Enter","OK")

Insert a column between your now A & B moving your B to C;
In B3 enter:
=IF(ISERR(OR(SEARCH("(",A3,1)=FALSE,SEARCH(")",A3,1)=FALSE)),"Bad Number-
Re-Enter","OK")
and copy down.
HTH

 

[jaimy]

I cannot change the column now. There are other data in the columns.

Is there other way?

Please advice.

Thanks

 

[Guest]

Jaimy,

How about selecting the user input area and utilize conditional formatting and insert JMay's formula, with minor modifications, in the field.

=IF(ISERR(OR(SEARCH("(",RIGHT(A3,3),1)=FALSE,SEARCH(")",RIGHT(A3,3),1)=FALSE
)),1,0)

I took out the the display strings "Bad Number- Re-Enter" and "OK" and replaced them with 1 and 0 respectively. Then apply a formatting that highlights invalid inputs, like BOLD and RED. Also, please note that A3's conditional formatting is the above; for other input cells, just change the "A3" to the actual address of the cell to which the conditional formatting applies to. Then create a legend of some sort explaining that the BOLD and RED formatting was applied because the user inputted an invalid project number.

Another way is actually create a list of ALL the project numbers on a separate sheet and use Data Validation and change the "Any value" to "List". Then just enter the cell range of the list of project numbers. This may be time consuming depending on the size of the list.

Ron_D

 

[jaimy]

Any one know how to do this?

Please help advice.

 

[Dave Peterson]

You could try to catch it when they're typing in the values:

right click on the worksheet tab that should have this behavior and select View
Code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastChar As String
Dim FirstChars As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
LastChar = UCase(Right(.Value, 1))
FirstChars = Left(.Value, Len(.Value) - 1)
If LastChar Like "[A-Z]" Then
If IsNumeric(FirstChars) Then
Application.EnableEvents = False
.Value = FirstChars & "(" & LastChar & ")"
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


I looked for #####@ (numbers followed by a letter). If I found it, it'll
convert it to #####(@).

If you want to fix some values that already look like #####@, you could add this
to a general module.

The select your range and tools|macro|macros...|and run it. (Give it a nicer
name if you're going to keep it around):

Option Explicit
Sub testme01()

Dim LastChar As String
Dim FirstChars As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a nice range first!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
LastChar = UCase(Right(.Value, 1))
FirstChars = Left(.Value, Len(.Value) - 1)
If LastChar Like "[A-Z]" Then
If IsNumeric(FirstChars) Then
Application.EnableEvents = False
.Value = FirstChars & "(" & LastChar & ")"
End If
End If
End With
Next myCell

End Sub

 

[jaimy]

Yes, this is something I want. How come I never think of it.

Thanks a lot, Dave

 

[Dave Peterson]

I meant to ask how you should process plain numbers.

In your original message, you wrote:

The problem now is some of the project numbers are like this 1100,
1100(A), 1307(E), 1611(A), 1611(B), 1612(C), 1612(D), 10303(1) and
etc.

So 1100 stays the same, but 103031 would be converted to 10303(1).

I chose to ignore all numbers. I didn't see an obvious rule, so you'll have to
fix those manually.

 

[digbyp]

Am looking for a similar macro

I have 700 lines that has two columns with dates in, format i
27/06/1950.

I want the result to be "27/06/1950"

Therefore all I need is the " to be placed around the date


Many thank

 

[mnewdick]

"HTH" has the answer ... or use a Form to have user enter data, and
include a drop down.

 

[digbyp]

What is "HTH"?

Remember, I already have 700 lines with date in and need to amend this
data

 

[Gord Dibben]

HTH = Hope This Helps

To find acronymns see.......

http://www.ucc.ie/cgi-bin/acronym/acro.html

HTH Gord Dibben Excel MVP

 

[digbyp]

Great, but what about the orginal question

 

[Gord Dibben]

Which was?

Gord

 

[Dave Peterson]

How about using an extra column or two:

=""""&TEXT(A1,"dd/mm/yyyy")&""""

And drag down.

If you want to, copy|paste special|values right over the original range and
delete the helper column.