Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select text from a field
Hi all,
This group has been very helpful to me and Thank you for your help and quick respond. I don't think this is duable, but since I do not have programming experience, I thought asking you. I have a sheet that has 5000 records. User has to fill out column B with the correct name, based on a column A I provided. E.g. Col A HOUSE OF EUROPE FUNDING IV PLC In col B he has to fix this name and enter: FUNDING IV PLC He didn't do so . Instead he highlighted BOLD in col A "FUNDING IV PLC" and said use in col B highlighted name. Question: Is there any way for me to say update Col B with part of the name that is Bold on col. A? Otherwise I have to do this manually for 500 rec. Thanks a lot -- Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select text from a field
Hi,
The following macro looks at each cell in the selected range and copy the non-bold text to the next cell on its right. Eg -- To process A1:A10 - make sure there is no data in B1:B10 (it will be the output), else insert a column - select A1:A10 - run the macro -- the output is sent to B1:B10 so you can review and make sure it is what you want before deleting cells in A '----------------------------------------------------------------- Sub RemoveBoldText() Dim RgSource As Range, cell As Range Dim cellTxt As String, c As Characters, txtOut As String Dim cellLen As Long, i As Long Set RgSource = Selection Application.ScreenUpdating = False For Each cell In RgSource.Cells txtOut = "" cellLen = cell.Characters.Count For i = 1 To cellLen If cell.Characters(i, 1).Font.Bold = False Then txtOut = txtOut & cell.Characters(i, 1).Text End If Next cell.Offset(0, 1) = txtOut Next End Sub '------------------------------------------------------------ -- Regards, Sébastien <http://www.ondemandanalysis.com "Bob" wrote: Hi all, This group has been very helpful to me and Thank you for your help and quick respond. I don't think this is duable, but since I do not have programming experience, I thought asking you. I have a sheet that has 5000 records. User has to fill out column B with the correct name, based on a column A I provided. E.g. Col A HOUSE OF EUROPE FUNDING IV PLC In col B he has to fix this name and enter: FUNDING IV PLC He didn't do so . Instead he highlighted BOLD in col A "FUNDING IV PLC" and said use in col B highlighted name. Question: Is there any way for me to say update Col B with part of the name that is Bold on col. A? Otherwise I have to do this manually for 500 rec. Thanks a lot -- Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select text from a field
I don't think my previous post went through so here goes again. With the
following in cell a21 (bold doesn't show.): Now is the time for all good men. time for all good men. is bold wrote the following sub and in immediate pane, debug.prints output was: time for all good men. So, your objective appears doable. Sub testthis() Dim ws As Worksheet Dim test As String Dim NewVal As String Dim i As Integer, L As Integer Set ws = ThisWorkbook.Worksheets("sheet1") NewVal = "" test = ws.Range("a21").Value With ws.Range("a21") L = Len(test) For i = 1 To L ' If Mid(test, i, 1).Font.Bold = True Then If .Characters(i, 1).Font.Bold = True Then NewVal = NewVal + Mid(test, i, 1) End If Next i End With Debug.Print NewVal End Sub "Bob" wrote: Hi all, This group has been very helpful to me and Thank you for your help and quick respond. I don't think this is duable, but since I do not have programming experience, I thought asking you. I have a sheet that has 5000 records. User has to fill out column B with the correct name, based on a column A I provided. E.g. Col A HOUSE OF EUROPE FUNDING IV PLC In col B he has to fix this name and enter: FUNDING IV PLC He didn't do so . Instead he highlighted BOLD in col A "FUNDING IV PLC" and said use in col B highlighted name. Question: Is there any way for me to say update Col B with part of the name that is Bold on col. A? Otherwise I have to do this manually for 500 rec. Thanks a lot -- Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select text from a field
Bob,
Try this function: Function getboldtext(sz_cell As String) Dim i As Long Dim s As String s = "" For i = 1 To Len(Range(sz_cell)) If Range(sz_cell).Characters(i, 1).Font.Bold = True Then s = s & Range(sz_cell).Characters(i, 1).Text End If Next i getboldtext = s End Function and put in column b =getboldtext("A1") "Bob" wrote: Hi all, This group has been very helpful to me and Thank you for your help and quick respond. I don't think this is duable, but since I do not have programming experience, I thought asking you. I have a sheet that has 5000 records. User has to fill out column B with the correct name, based on a column A I provided. E.g. Col A HOUSE OF EUROPE FUNDING IV PLC In col B he has to fix this name and enter: FUNDING IV PLC He didn't do so . Instead he highlighted BOLD in col A "FUNDING IV PLC" and said use in col B highlighted name. Question: Is there any way for me to say update Col B with part of the name that is Bold on col. A? Otherwise I have to do this manually for 500 rec. Thanks a lot -- Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select text from a field
after re-readigg, seems like you wanted the bold portion , not the non-bold
part. If so, chnage the False in If cell.Characters(i, 1).Font.Bold = False Then to True -- Regards, Sébastien <http://www.ondemandanalysis.com "sebastienm" wrote: Hi, The following macro looks at each cell in the selected range and copy the non-bold text to the next cell on its right. Eg -- To process A1:A10 - make sure there is no data in B1:B10 (it will be the output), else insert a column - select A1:A10 - run the macro -- the output is sent to B1:B10 so you can review and make sure it is what you want before deleting cells in A '----------------------------------------------------------------- Sub RemoveBoldText() Dim RgSource As Range, cell As Range Dim cellTxt As String, c As Characters, txtOut As String Dim cellLen As Long, i As Long Set RgSource = Selection Application.ScreenUpdating = False For Each cell In RgSource.Cells txtOut = "" cellLen = cell.Characters.Count For i = 1 To cellLen If cell.Characters(i, 1).Font.Bold = False Then txtOut = txtOut & cell.Characters(i, 1).Text End If Next cell.Offset(0, 1) = txtOut Next End Sub '------------------------------------------------------------ -- Regards, Sébastien <http://www.ondemandanalysis.com "Bob" wrote: Hi all, This group has been very helpful to me and Thank you for your help and quick respond. I don't think this is duable, but since I do not have programming experience, I thought asking you. I have a sheet that has 5000 records. User has to fill out column B with the correct name, based on a column A I provided. E.g. Col A HOUSE OF EUROPE FUNDING IV PLC In col B he has to fix this name and enter: FUNDING IV PLC He didn't do so . Instead he highlighted BOLD in col A "FUNDING IV PLC" and said use in col B highlighted name. Question: Is there any way for me to say update Col B with part of the name that is Bold on col. A? Otherwise I have to do this manually for 500 rec. Thanks a lot -- Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble sorting first by a dates field and then by a text field. | Excel Discussion (Misc queries) | |||
Changing a text field to a date field | New Users to Excel | |||
How to keep leading zero without changing field to text field? | Excel Discussion (Misc queries) | |||
How do I select only specific parts of one field? | Excel Worksheet Functions | |||
HELP!! Populate text in single XL field, or bulk copy text into 1 field | Excel Programming |