I need to create 2 cells from one cell based on Bold Type. The Bold type
characters (name) will be copied into one new cell with the rest of the
characters (registration number, color(bay/chestnut) and dates) in a 2nd new
cell. The original cell can contain up to 28 alpha/numeric characters. How
can I count the number of Bold Type characters in a cell? I will be working
with several hundred original mixed cells.

EX:NAME ahr#1234567 1989 (with NAME being bold type.)

Thanks in advance for your help!!

ONe wa:

Public Function BoldChars(ByRef rCell As Excel.Range) As Variant
Dim nCount As Long
Dim i As Long
With rCell
If .Cells.Count > 1 Then
BoldChars = CVErr(xlErrRef)
Else
For i = 1 To .Characters.Count
nCount = nCount - .Characters(i, 1).Font.Bold
Next i
BoldChars = nCount
End If
End With
End Function


In article <7C1245CB-6398-415A-AA08-(E-Mail Removed)>,
Duddly Dummy <Duddly (E-Mail Removed)> wrote:

> I need to create 2 cells from one cell based on Bold Type. The Bold type
> characters (name) will be copied into one new cell with the rest of the
> characters (registration number, color(bay/chestnut) and dates) in a 2nd new
> cell. The original cell can contain up to 28 alpha/numeric characters. How
> can I count the number of Bold Type characters in a cell? I will be working
> with several hundred original mixed cells.
>
> EX:NAME ahr#1234567 1989 (with NAME being bold type.)
>
> Thanks in advance for your help!!

Possible alternative?

First....Create this UDF in a general module:

'--------Start_of_Code---------
'PullBold: returns from the 1st bold char in a cell through the last
'contiguous bold char (ignoring subsequent bold chars)

Public Function PullBold(ByRef RefCell As Range) As Variant
Dim strText As String
Dim iRefLen As Integer
Dim iChrCtr As Integer
Dim oChar As Characters
Dim blnHasBold As Boolean

Dim strBullpen
strBullpen = ""

Set RefCell = RefCell.Cells(1.1)

iRefLen = Len(RefCell.Text)
If iRefLen > 0 Then
With RefCell
For iChrCtr = 1 To Len(RefCell.Text)
Set oChar = .Characters(iChrCtr, 1)

If oChar.Font.FontStyle = "Bold" Then
blnHasBold = True
strBullpen = strBullpen & oChar.Text
Else
If blnHasBold = True Then
Exit For
End If
End If
Next iChrCtr
End With 'RefCell
End If
PullBold = strBullpen
End Function
'--------End_of_Code---------

Then....in your workbook
If A1: Lily of the Valley ahr#1234567 1989
(with "Lily of the Valley" being bold type.)

B1: =PullBold(A1)
Returns: Lily of the Valley

C1: =TRIM(SUBSTITUTE(A1,B1,""))
Returns: ahr#1234567 1989

Copy both functions down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Duddly Dummy" wrote:

> I need to create 2 cells from one cell based on Bold Type. The Bold type
> characters (name) will be copied into one new cell with the rest of the
> characters (registration number, color(bay/chestnut) and dates) in a 2nd new
> cell. The original cell can contain up to 28 alpha/numeric characters. How
> can I count the number of Bold Type characters in a cell? I will be working
> with several hundred original mixed cells.
>
> EX:NAME ahr#1234567 1989 (with NAME being bold type.)
>
> Thanks in advance for your help!!
>
>

I have never seen the syntax (ByRef rCell As Excel.Range).
Can you please expand on the 'Excel.Range' data type
Thanks
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> ONe wa:
>
> Public Function BoldChars(ByRef rCell As Excel.Range) As Variant
> Dim nCount As Long
> Dim i As Long
> With rCell
> If .Cells.Count > 1 Then
> BoldChars = CVErr(xlErrRef)
> Else
> For i = 1 To .Characters.Count
> nCount = nCount - .Characters(i, 1).Font.Bold
> Next i
> BoldChars = nCount
> End If
> End With
> End Function
>
>
> In article <7C1245CB-6398-415A-AA08-(E-Mail Removed)>,
> Duddly Dummy <Duddly (E-Mail Removed)> wrote:
>
>> I need to create 2 cells from one cell based on Bold Type. The Bold type
>> characters (name) will be copied into one new cell with the rest of the
>> characters (registration number, color(bay/chestnut) and dates) in a 2nd
>> new
>> cell. The original cell can contain up to 28 alpha/numeric characters.
>> How
>> can I count the number of Bold Type characters in a cell? I will be
>> working
>> with several hundred original mixed cells.
>>
>> EX:NAME ahr#1234567 1989 (with NAME being bold type.)
>>
>> Thanks in advance for your help!!

Thanks for responding quickly!! I will try this and let you know.

"Ron Coderre" wrote:

> Possible alternative?
>
> First....Create this UDF in a general module:
>
> '--------Start_of_Code---------
> 'PullBold: returns from the 1st bold char in a cell through the last
> 'contiguous bold char (ignoring subsequent bold chars)
>
> Public Function PullBold(ByRef RefCell As Range) As Variant
> Dim strText As String
> Dim iRefLen As Integer
> Dim iChrCtr As Integer
> Dim oChar As Characters
> Dim blnHasBold As Boolean
>
> Dim strBullpen
> strBullpen = ""
>
> Set RefCell = RefCell.Cells(1.1)
>
> iRefLen = Len(RefCell.Text)
> If iRefLen > 0 Then
> With RefCell
> For iChrCtr = 1 To Len(RefCell.Text)
> Set oChar = .Characters(iChrCtr, 1)
>
> If oChar.Font.FontStyle = "Bold" Then
> blnHasBold = True
> strBullpen = strBullpen & oChar.Text
> Else
> If blnHasBold = True Then
> Exit For
> End If
> End If
> Next iChrCtr
> End With 'RefCell
> End If
> PullBold = strBullpen
> End Function
> '--------End_of_Code---------
>
> Then....in your workbook
> If A1: Lily of the Valley ahr#1234567 1989
> (with "Lily of the Valley" being bold type.)
>
> B1: =PullBold(A1)
> Returns: Lily of the Valley
>
> C1: =TRIM(SUBSTITUTE(A1,B1,""))
> Returns: ahr#1234567 1989
>
> Copy both functions down as far as you need.
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Duddly Dummy" wrote:
>
> > I need to create 2 cells from one cell based on Bold Type. The Bold type
> > characters (name) will be copied into one new cell with the rest of the
> > characters (registration number, color(bay/chestnut) and dates) in a 2nd new
> > cell. The original cell can contain up to 28 alpha/numeric characters. How
> > can I count the number of Bold Type characters in a cell? I will be working
> > with several hundred original mixed cells.
> >
> > EX:NAME ahr#1234567 1989 (with NAME being bold type.)
> >
> > Thanks in advance for your help!!
> >
> >

The default object for the Range property is Application, in this case
Excel.

Not usually required, but if one is working with more than one app, it
disambiguates which app's Range is to be used.

Don't remember if all versions of XL automatically use Excel.Range for
the arguments of the event macros they generate, but all versions I've
checked do.

In article <(E-Mail Removed)>,
"Bernard Liengme" <(E-Mail Removed)> wrote:

> I have never seen the syntax (ByRef rCell As Excel.Range).
> Can you please expand on the 'Excel.Range' data type