vba - How can I Extract only the text from cells which are formatted in a specific way. (excel) -

-

hi wonder if can help. importing html file places text first column of spreadsheet. text appear formated in various ways example may of colour or in italics, bold or combination.

is there way extract various text column based upon it's format , copy text column vba? please see example below of achieve.

many in advance.

link shows screen shot of excel , achieve

you may start code

option explicit  sub main()     dim cell range     dim word variant, coloff variant     dim coloffstrng string      worksheets("mysheet") '<~~ change "mysheet" whatever name sheet has         each cell in .range("a4:a" & .cells(.rows.count, 1).end(xlup).row).specialcells(xlcelltypeconstants, xltextvalues) '<~~consider cells in column row 4 last non empty cell containing text values             each word in split(worksheetfunction.trim(cell)) '<~~ check each word in cell. space assumed words separator, multiple spaces considered 1 space                 coloffstrng = "" '<~~ initialize offsets string                 cell.characters(instr(cell, word), len(word)).font '<~~ consider font property of word first character                     if .bold coloffstrng = coloffstrng & "1 " '<~~ bold 1 column offset column                     if .italic coloffstrng = coloffstrng & "2 " '<~~ italic 2 columns offset column                     select case .colorindex '<~~ consider colorindex property of word first character                       case 3 'red                           coloffstrng = coloffstrng & "3 " '<~~ red 3 columns offset column                       case 5 'blue                           coloffstrng = coloffstrng & "4 " '<~~ blue 4 columns offset column                     end select                 end                 each coloff in split(trim(coloffstrng)) '<~~ loop through each column offset stored                     cell.offset(, coloff) = word '<~~ fill proper cell word                 next coloff             next word         next cell     end end sub

where assumed following:

  • all characters of each word share same formatting

  • formatting style columns are:

    • column b bold words

    • column c italic words

    • column d red words (colorindex = 3)

    • column e blue words (colorindex = 5)

    • for combination may use formulas directly typed in cells, like

      =if(c4=d4,c4,"") in cell h4, put combination of "italic" , "red" formatting

of course "combination" can furtherly automatized, perhaps boolean algebra


Comments

Post a Comment

Popular posts from this blog

ios - RestKit 0.20 — CoreData: error: Failed to call designated initializer on NSManagedObject class (again) -

-
this error has shown on many times, seems. prominent answer issue seems restkit 0.20 — coredata: error: failed call designated initializer on nsmanagedobject class however, solution posted doesn't work me. let's start beginning: i want upload file server. want parse response make sure uploaded. if not, try again later. model has boolean variable called nsnumber *senttoserver purpose (in coredata, bool saved nsnumber ) . because don't know whether sending going successful, save entity coredata model first before sending via restkit. when post request successful, server returns json string this: {"id":14,"created":true} notice that, of course, id not set on coredata model yet, both id , created new informations model. here's model: @interface recording : nsmanagedobject @property (nonatomic, retain) nsstring * audiofilepath; @property (nonatomic, retain) nsdate * createdat; @property (nonatomic, retain) nsstring * deviceid; @...
Read more

java - Digest auth with Spring Security using javaconfig -

-
so i'm trying create digest authentication spring following documentation trying translate xml "requirements" in java requirements. let's have xml in docs: <bean id="digestfilter" class= "org.springframework.security.web.authentication.www.digestauthenticationfilter"> <property name="userdetailsservice" ref="jdbcdaoimpl"/> <property name="authenticationentrypoint" ref="digestentrypoint"/> <property name="usercache" ref="usercache"/> </bean> <bean id="digestentrypoint" class= "org.springframework.security.web.authentication.www.digestauthenticationentrypoint"> <property name="realmname" value="contacts realm via digest authentication"/> <property name="key" value="acegi"/> <property name="noncevalidityseconds" value="10"/> </b...
Read more

laravel - PDOException in Connector.php line 55: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) -

-
my .env: app_env=local app_debug=true app_key=myappkey app_url=http://localhost db_connection=mysql db_host=localhost db_port=3306 db_database=gestiontaller db_username=root db_password='root' cache_driver=file session_driver=file queue_driver=sync redis_host=127.0.0.1 redis_password=null redis_port=6379 mail_driver=smtp mail_host=mailtrap.io mail_port=2525 mail_username=null mail_password=null mail_encryption=null i've tried password ", ' , without, restarting server every time. i've tried changing localhost 172.0.0.1. yes, password correct, running mysql -u root -p introducing password root in prompt can go mysql. users table: +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | | root1 | localhost | +------------------+-----------+ i've added homestead.yaml this: database_port: 3306 ...
Read more