Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I count BOLD letters in a cell?
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I count BOLD letters in a cell?
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 , Duddly Dummy <Duddly 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I count BOLD letters in a cell?
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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I count BOLD letters in a cell?
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" wrote in message ... 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 , Duddly Dummy <Duddly 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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I count BOLD letters in a cell?
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I count BOLD letters in a cell?
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 , "Bernard Liengme" wrote: I have never seen the syntax (ByRef rCell As Excel.Range). Can you please expand on the 'Excel.Range' data type |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i count how many letters A from cell e10 to sell h10 ? | Excel Discussion (Misc queries) | |||
How to count number of Cell have Strike Through Effect & Bold as font style. | Excel Discussion (Misc queries) | |||
Data to column by bold letters | Excel Programming | |||
Extract bold letters in a cell | Excel Worksheet Functions | |||
Extract bold letters in a cell | Excel Worksheet Functions |