regex,string,excel-vba,character,number-formatting , (VBA Excel) Extract Text and related Letter from String and output result


(VBA Excel) Extract Text and related Letter from String and output result

Question:

Tag: regex,string,excel-vba,character,number-formatting

So the scenario I have is there are letter with a number:

enter image description here

Desired Output after Program (Note an underscore is visually used but I need a space:

____________F
__G
_____E
__G__E______F

Currently I have written code in the Number and First Letter Column to extract the number and first letter:

First Letter:

LEFT(A2,1)

Number:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Now my VBA Script can take the number and character to get the information and output for (For ONE Error Code):

Private Sub Code_Printer_Click()

Dim myFile As String, rng As Range, cellValue As Variant, I As Integer, j As Integer

myFile = "C:\Reformatted.txt"
Set rng = Selection

Open myFile For Output As #1

For I = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count

If j = rng.Columns.Count Then
    cellValue = Space(rng.Cells(I, 1)) + CStr(rng.Cells(I, 2).Value)
    Print #1, cellValue
End If

    Next j
    cellValue = ""
Next I

Close #1
Shell "C:\Windows\Notepad.exe C:\Reformatted.txt", 1

End Sub

RESULT:

Result

So please help me process multiple codes in the same event.

If an excel function needs to be done thats fine. If its easier to extract the number in VBA thats ok to. Non technical people will use so the more VBA the better. Please let me know if this is a slow or if there is a faster, easier way to do this! :)

-----------------------------Final Data--------------------------------------- FinalData


Answer:

This is how I'd do it -

Sub test()
Dim myFile As String
myFile = "C:\reformatted.txt"
Open myFile For Output As #1
Dim iPar As Integer
Dim sChar As String
Dim sBlank As Long
Dim cont As Boolean

Dim mystring As String

For Each c In Range("A:A")
If c <> "" Then
'Get first
        iPar = InStr(1, c, "(")
        If Mid(c, iPar - 1, 1) = "" Then
            If Mid(c, iPar - 2, 1) = "" Then
            sChar = Mid(c, iPar - 3, 1)
            Else: sChar = Mid(c, iPar - 2, 1)
            End If
        Else: sChar = Mid(c, iPar - 1, 1)
        End If
        If IsNumeric(Mid(c, iPar + 1, 2)) Then
            sBlank = Mid(c, iPar + 1, 2)
        Else: sBlank = Mid(c, iPar + 1, 1)
        End If
        mystring = Space(sBlank) & sChar
        cont = InStr(iPar + 1, c, "(")

    Do While cont = True

        iPar = InStr(iPar + 1, c, "(")
        If Mid(c, iPar - 1, 1) = "" Then
            If Mid(c, iPar - 2, 1) = "" Then
            sChar = Mid(c, iPar - 3, 1)
            Else: sChar = Mid(c, iPar - 2, 1)
            End If
        Else: sChar = Mid(c, iPar - 1, 1)
        End If
        If IsNumeric(Mid(c, iPar + 1, 2)) Then
            sBlank = Mid(c, iPar + 1, 2)
        Else: sBlank = Mid(c, iPar + 1, 1)
        End If

        If sBlank + 1 > Len(mystring) Then
            mystring = mystring & Space(sBlank - Len(mystring)) & sChar
        Else: mystring = Application.WorksheetFunction.Replace(mystring, sBlank + 1, 1, sChar)
        End If
        cont = InStr(iPar + 1, c, "(")

    Loop

       Print #1, mystring
     Else: Exit For
   End If
Next
Close #1
Shell "C:\Windows\Notepad.exe C:\reformatted.txt", 1

End Sub

So it searches for the first parentheses ( and tackles the character and spaces. Then it looks for another ( - if it finds one, it continues, otherwise it's done (and prints).

If it continues, it finds the next ( and does the same test finding the character and spaces, then checks the string to see if the length of the string is greater than the number of spaces. If it is, it replaces a blank character with your character. If not, it appends spaces and then inserts the character at the end.

Then it searches again for a ( to repeat the process.

Right now it is searching column A and printing in column B - adjust as needed. You can print mystring to file.


Related:


Regex with whitespaces and preceding zeros


regex,sas
I want to match the string 11 with a regular Expression in SAS. The 11 can be preceded by zero or more 0 and/or by white spaces. Any other character is not allowed. Likewise, if anything there should only be white spaces following the 11. Examples: Match: 0000011 11 11<space><space>...

Get all prices with $ from string into an array in Javascript


javascript,regex,currency
var string = 'Our Prices are $355.00 and $550, down form $999.00'; How can I get those 3 prices into an array?...

Characters and Strings in Swift


ios,string,swift,unicode,character
Reading the documentation and this answer, I see that I can initialize a Unicode character in either of the following ways: let narrowNonBreakingSpace: Character = "\u{202f}" let narrowNonBreakingSpace = "\u{202f}" As I understand, the second one would actually be a String. And unlike Java, both of them use double quotes...

Match a pattern preceded by a specific pattern without using a lookbehind


regex,eclipse,lookahead
Is there a way to match a B only if preceded by an A? The A can be at any position behind the B, with any amount of characters between. Examples: A_B (Matches `B`) C_B (No match) I've tried: (?=A)[^B]*B But it matches all the characters preceeding B as well....

How to create the javascript regular expression for number with some special symbols


javascript,regex
what can be the java-script regular expression which gives the numbers with some symbols For example following condition must be pass. Number can start with $ Can have the . or , : symbols between and % sign at the send. Passing valus: $233 48.23% 278 22.33 45:23 10,000 Number...

C++11 Allocation Requirement on Strings


c++,string,c++11,memory,standards
I had heard that C++11 was going to require strings to be allocated in contiguous memory. I even thought I saw a stack overflow question on it, but I can't seem to find it. I know that in practice both gcc and Visual Studio do allocate strings contiguously, I'm just...

C++ & Qt: Random string from an array area


c++,arrays,string,qt,random
In my small Qt application, I want to pick a random string out of an array after I clicked on a button. I've read many threads but nothing works for me. So in my slot there's an array with several strings in it. I also implemented <string>, <time.h> and srand....

Android String if-statement


java,android,string
I have a if-statement in the start of my app if (ready.equals("yes")){ ... } and later on my code I have ready="yes"; but the if statement is never called, why? The ready="yes"; is called from a background thread, is that why? public void DownloadFromUrl(final String fileName) { //this is the...

How to match words in 2 list against another string of words without sub-string matching in Python?


python,regex,string,loops,twitter
I have 2 lists with keywords in them: slangNames = [Vikes, Demmies, D, MS Contin] riskNames = [enough, pop, final, stress, trade] i also have a dictionary called overallDict, that contains tweets. The key value pairs are {ID: Tweet text) For eg: {1:"Vikes is not enough for me", 2:"Demmies is...

How To Check Value Of String


javascript,css,string,numeric
<span id='amount'>0.00000000</span> <a class='button-withdraw' id='tombolco' href='#'>Checkout</a> <script> var amount = document.getElementById("amount").innerHTML; if (amount >= 0.001) { document.GetElementById("tombolco").style = "display:block"; } else { document.GetElementById("tombolco").style = "display:none"; } </script> Why my code doesn't work? What's wrong with it and how to solve it?...

Warning: preg_match_all(): Unknown modifier '\' [duplicate]


php,regex,warnings
This question already has an answer here: PHP - preg_match and “Unknown modifier” error 3 answers I know there are tons of duplicate question like this. But I am stuck here. Can anyone help me out? Here's the code: <?php function count_images($string){ preg_match_all('/[^/\s]+/\S+\.(jpg|png|gif)/', $string, $results); return count($results[1]); } ?>...

.cpp:23: error: cannot convert ‘std::string’ to ‘const char*’ for argument ‘1’ to ‘int atoi(const char*)’


c++,string
Here a basic code I'm trying to run But I'm having trouble with stoi (it's c++) I keep getting error: ‘stoi’ was not declared in this scope I tried atoi and strtol with this error .cpp:23: error: cannot convert ‘std::string’ to ‘const char*’ for argument ‘1’ to ‘int atoi(const char*)’...

What is the usage of adding an empty string in a javascript statement


javascript,string,concatenation
I see an empty string ('' or "") used in many JavaScript statements but not sure what does it stand for. e.g. var field = current.condition_field + ''; Can someone please clarify?...

Regex that allow void fractional part of number


c#,regex
@"[+-]?\d+(\.\d+)?" -this is a regex I have wrote for numbers it allows [+-] minus before the number digits before and digits after the point the question is how to change this to allow "not finished" values so that input of "5." - is fine too ?...

Validate part of mail suffix


c#,regex
I'm validating mail addresses that could look something like this: [email protected] but also [email protected] I would like to check whether if xx is present or not. The first thing that came to mind was String.Contains, but it would obviously match any occurrences of xx in the string. Is regexp the...

Does there exist an algorithm for iterating through all strings that conform to a particular regex?


c#,regex,algorithm
I'm making a script to try and hack into an account whose login password is at least 8 characters long and includes at least 1 number, 1 special character and 1 capital letter. I will use brute force. Is there a compact, elegant and efficient way to iterate through every...

REGEX python find previous string


python,regex,string
I'm trying to find if the last word of the string is followed by a space or a special char, and if yes return the string without this space/special char For example : "do you love dogs ?" ==> return "do you love dogs" "i love my dog " (space...

Extracting strings from HTML with Python wont work with regex or BeautifulSoup


python,regex,parsing,beautifulsoup,python-requests
Im using Python 2.7, BeautifulSoup4, regex, and requests on windows 7. I've scraped some code from a website and I am having problems parsing and extracting the bits I want and storing them in a dictionary. What I'm after is text that is presented as follows in the code: @CAD_DTA\">I...

Regex to remove `.` from a sub-string enclosed in square brackets


c#,.net,regex,string,replace
I have this regex in C#: \[.+?\] This regex extracts the sub-strings enclosed between square brackets. But before doing that I want to remove . inside these sub-strings. For example, the string hello,[how are yo.u?]There are [300.2] billion stars in [Milkyw.?ay]. should become hello,[how are you?]There are [3002] billion stars...

Memory consumption when chaining string methods


c#,string,immutability,method-chaining
I know that string in C# is an immutable type. Is it true that when you chain string functions, every function instantiates a new string? If it is true, what is the best practice to do too many manipulations on a string using chaining methods?...

Please can someone help me understand the exec method for regular expressions?


javascript,regex
The best place I have found for the exec method is Eloquent Javascript Chapter 9: "Regular expressions also have an exec (execute) method that will return null if no match was found and return an object with information about the match otherwise. An object returned from exec has an index...

How many characters are visible like a space, but are not space characters?


php,regex
If I want to discover the hexadecimal equivalent of a space in PHP I can play with bin2hex: php > echo var_dump(bin2hex(" ")); string(2) "20" I can also obtain space character from "20" php > echo var_dump(hex2bin("20")); string(1) " " But there exist Unicode versions of a "visible" space: php...

How to Match a string with the format: “20959WC-01” in php?


php,regex
i want to restrict a user to enter a value which is similar to the value "20959WC-01", means it must contains 5 integers followed by two character, a '-' and two integers, can anyone please give me a solution to sort out this problem. Thanks in advance :) ...

Identify that a string could be a datetime object


python,regex,algorithm,python-2.7,datetime
If I knew the format in which a string represents date-time information, then I can easily use datetime.datetime.strptime(s, fmt). However, without knowing the format of the string beforehand, would it be possible to determine whether a given string contains something that could be parsed as a datetime object with the...

Regular Expression for whole world


regex,c#-4.0,vb6
First of all, I use C# 4.0 to parse the code of a VB6 application. I have some old VB6 code and about 500+ copies of it. And I use a regular expression to grab all kinds of global variables from the code. The code is described as "Yuck" and...

MySQL substring match using regular expression; substring contain 'man' not 'woman'


mysql,regex
I have an issue while I fetch data from database using regular expression. While I search for 'man' in tags it returns tags contains 'woman' too; because its substring. SELECT '#hellowomanclothing' REGEXP '^(.)*[^wo]man(.)*$'; # returns 0 correct, it contains 'woman' SELECT '#helloowmanclothing' REGEXP '^(.)*[^wo]man(.)*$'; # returns 0 incorrect, it can...

byte array to string and inverse : recoverded byte array is NOT match to original byte array in Java


java,string,bytearray
I converted a byte array to string as follow, in Java: String str_bytearray = new String(bytearray_original); and then, I recovered original byte array using string, as follow: byte[] bytearray_recovered = str_bytearray.getBytes(); but I wondered when I compared bytearray_original and bytearray_recovered. the result is as follow: [48, 89, 48, 19, 6,...

regex - Match filename with or without extension


regex,logstash-grok
Need a regex pattern to match all of the following: hello hello. hello.cc I tried \b\w+\.?\w+?\b, but this doesn't match "hello." (the second string mentioned above)....

Reg ex matching a word


regex
I need to match only first two files, out of four files listed below: ABD_DEF_GHIJ_20150611 ABD_DEF_GHIJ ABD_DEF_GHIJ_FX_20150611 ABD_DEF_GHIJ_FX I am using reg ex - ABD_DEF_GHIJ(_\d{8}|\b) and it's working fine. I would like to know if my solution is ok or there is any better alternate solution....

Remove quotes to use result as dataset name


r,string
I've got a vector with a long list of dataset names. E.g myvector<-c('ds1','ds2,'ds3') I'd like to use the names ds1..ds3 to write a file, taking the file name from the vector. Like this: write.csv(dataset[i],file=paste(myvector[i],'.csv',sep='') with dataset being d1...ds3, but without quotes. How can I remove the quotes and refer to...

Regex not working in HTML5 pattern


regex,html5
So I have this regex intended to let pass all text but those that contain as initial chars the "34" sequence: ^(?!34)(?=([\w]+)) The regex is working fine for me in https://regex101.com/r/iN1yN3/2 , check the tests to see the intended behavior. Any Idea why it isn't working in my form? <form>...

Java - Enforce TextField Format - UX - 00:00:00;00


java,regex,user-interface
Please see below image of the UI of my current application Currently, I am enforcing Data Format of the text fields in the back end. If the RegEx does match (00:00:00;00), it will throw an error. My question is from a UX perspective, is it possible to make the input...

How to write RegEx for inserting line break for line length more than 30 characters?


regex
I am using a text editor which lets use regular expression to find / replace text. I have a large text file. I want to insert new line in each lines which are more than 30 characters. I want the line to break after 30th character (doesnt matter if a...

Python regular expression, matching the last word


python,regex,list
I've the following problem. I'm looking to find all words in a string that typically looks like so HelloWorldToYou Notice, each word is capitalized as a start followed by the next word and so on. I'm looking to create a list of words from it. So the final expected output...

PHP Regular Expressions Counting starting consonants in a string


php,regex
I need to find out how many starting consonants a word has. The number is used later in the program. The code below does work, I am wondering if it is possible to do this with a regular expression. $mystring ="SomeStringExample"; $mystring2 =("bcdfghjklmnpqrstvwxyzABCDFGHJKLMNPQRSTWVXYZ"); $var = strspn($mystring, $mystring2); Using a regular...

A beginner questions about printf, java


java,string,printf
I'm learning Java by myself and through tutorials online. Just wondering in a printf statement, what does the different %s, %d, %15, %7, %12.2(and so on...) mean? Couldn't find any explanation anywhere online, so I'm turning to you. ...

Comparing cell contents against string in Excel


string,excel,if-statement,comparison
Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

How can I convert an int to a string in C++11 without using to_string or stoi?


c++,string,c++11,gcc
I know it sounds stupid, but I'm using MinGW32 on Windows7, and "to_string was not declared in this scope." It's an actual GCC Bug, and I've followed these instructions and they did not work. So, how can I convert an int to a string in C++11 without using to_string or...

Translating a character array into a integer string in C++


c++,arrays,string
I was trying to achieve translating a character array into a integer string and corresponding character to their alphabetical order. For instance: A(a) = 0 , Z(z) = 25. string key_char = argv[1]; string key_num; for (int i = 0; i < key_char.length(); i++){ if (isalpha(key_char[i])){ if (islower(key_char[i])){ key_num[i] =...

SCALA: change the separator in Array


arrays,string,scala,delimiter
I have an Array like this. scala> var x=Array("a","x,y","b") x: Array[String] = Array(a, x,y, b) How do I change the separator comma in array to a :. And finally convert it to string like this. String = "a:x,y:b" My aim is to change the comma(separators only) to other separator(say,:), so...

Finding embeded xpaths in a String


java,regex
I have a string where I have the user should be able to specify xpaths that will be evaluated at runtime. I was thinking about having a the following way to specify it. String = "Hi my name is (/message/user) how can i help you with (/message/message) "; How can...

Swing regular expression for phone number validation


java,regex
I want to validate phone number field in swing, so I am writing code to allow user to enter only digits, comma, spaces. For this I am using regular expression, when user enter characters or other than the pattern text field will consume. My code is not working. Can anyone...

Get number from string


regex
I am trying to get the enclosed number between two slashes in a URL using regex. The code regex I have is not working, I am fairly new to regex and don't really understand it. The regex: http:\/\/?www\.?example\.com\/g\/(^\d$)\/\w The URL: http://www.example.com/g/1337/Game-Title Trying to get the "1337", which is the PlaceId....

Regex pass dynamic values with boundry


c#,regex,string,boundary
I'm trying to pass a dynamic value at runtime with a boundary \b to a Regex function. My code is: static void Main(string[] args) { string sent = "Accelerometer, gyro, proximity, compass, barometer, gesture, heart rate"; string match = "gyro"; string bound = @"\b"; if (Regex.IsMatch(sent, @"\bgyro", RegexOptions.IgnoreCase)) { Console.WriteLine("match...

match line break except line begin with spcific word or blank line


regex,notepad++
If I have text that the line breaks is broken: Chapter 1 Lorem ipsum dolor sit amet, consectetur adipisci ng elit, sed do eiusmod tempor incididunt ut la bore et dolore magna aliqua. Ut enim ad minim ve niam, quis nostrud exercitation ullamco labo ris nisi ut aliquip ex ea...

Store regex pattern as a string in PHP when regex pattern contains both single and double quotes


php,regex
UPDATE: It turned out that what broke my reg expressions after escaping the quotes was the / deliminator terminating the expression early. I'm attempting to store a regex pattern as a string like so: $link_pattern = "/<a\s+href=(?:"([^"]+)"|'([^']+)').*?>(.*?)</a>/"; However I'm unable to properly encapsulate it since the regex pattern contains both...