Want To Add Dashes To Cell Text...

May 11, 2009

I have a column of city names, some are one word and others are two or more. I am trying to take the city name and add it to another word, add dashes in between them and make everything lower case. For example:

Column 1, Cell 1 is: San Diego

I'd like it to look like: books-san-diego (books being the keyword I'd like to insert before the city.)

ADVERTISEMENT

Look Up On Cell But Based On Value Between Dashes

Jul 6, 2007

Here is the examples of values i have

TCH1 - John Smith - I - REG - Total
AV - John Doe - D - REG - Total

what i want to do is lookup on the value after the first dash and before the second dash to get the names only ; John smith, john doe

View 9 Replies View Related

How To Remove Specific Dashes Within Cell Leaving Others

Jul 28, 2014

I have a series of columns that have dashes in them and want to remove just specific dashes from the cell

Example of original 70489-344-15-001

This is what I want --> 70489-34415001

Got about 1000 of them ...

View 2 Replies View Related

Remove All The Dashes

Jun 14, 2006

Have a column containing data which may have 1-3 dashes locates within
each cell value.
Such as:
125-457A
158-857-1116
124184A-1
125-8745-22D9
458-2145-002-7

Would like to remove all the dashes "unless" the last dash to the right
is followed by a just a single digit. The results would be as follows:

125457A
1588571116
124184A-1
125874522D9
4582145002-7

On the example data, it kept 2 of the dashes in the cell values,
because the were only one digit away from the extreme right, but
removed all others.

View 9 Replies View Related

Return Cells That Have Exactly 3 Dashes

May 27, 2009

I have a column of data (column A) with cells that have IDs (alpha-numeric) that contain anywhere between 0 to 5 dashes. Since this column will change weekly, I want to dynamically pull only those cells that contain 3 dashes.

Example:
10648145-3026718-t526363536m
09wcg7-s1s89-wtpxm33-uu6jj0-9y8mv-v1
10658575-2349769-

I would only want to have returned 10648145-3026718-t526363536m since it contains 3 dashes. Do you know of a nifty formula for doing this? I think an array would get me the dynamic piece (so they are not spaced out).

View 3 Replies View Related

Remove Dashes In Number Automatically

Jan 13, 2008

I have a column of 1000 folio numbers and need a formula that will remove the dashes like so,

35-30-19-041-0180 into
3530190410180

View 4 Replies View Related

Date Entry Without Typing Dashes?

Feb 28, 2006

I want to type "121505" in a cell and it display 12-15-05. What formatting do I use?

View 4 Replies View Related

Removing Dashes Deletes Everything After First Dash

Mar 25, 2013

I have entries that look like this - 300-7188258-1

300-8983980-5

I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.

Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.

View 3 Replies View Related

Format Cells With Dashes Between Alphanumerics

Nov 15, 2006

I'm trying to find a way to have excel 2003 format the data I put in a cell to auto insert dashes every five characters. I'm entering product keys for the software we have on hand, and it would be a little quicker if I could simply have excel auto format the data entered in to include dashes every five characters.

Example:
- I would enter: D5ATT3D28F6F44536489413E2 (This is a fake, non-working product key, I just typed in random numbers a letters.)

-Excel would format it like: D5ATT-3D28F-6F445-36489-413E2

View 9 Replies View Related

How Do I Get The Dashes To Not Count As A Digit/number?

Sep 12, 2007

I have this script that does what i need it to do but one thing, I need the script to count the numbers as digits only and not the dashes in the format.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Len(Target) > 11) Or (Len(Target) < 10) Then
MsgBox "Not a valid number of characters, please try again"
Target.Value = vbNullString...................

View 11 Replies View Related

Hyperlink Code For Sheet Names With Spaces Or Dashes

Sep 7, 2007

I believe I have 99% of what I need. The code below has one bug. (See just the underlined text for a very quick overview. The rest is detail.)

My goal is to generate a TOC that:
1) has the VBA run from my Personal Workbook.
2) can be run in any worksheet of any open workbook.
3) can be run in a worksheet of any name.
4) will insert itself at the currently active cell.
5) has entries hyperlinking to the referenced worksheets - of any name.

Right now, I'm stuck with the last point. The macro below will generate the TOC at the currently active cell. The TOC will be hyperlinks to the referenced worksheets.

However, if the referenced worksheet contains a space, dash, or other special character then the generated hyperlink is broken. For example, if the referenced worksheet is "sheet1" then the hyperlink generated works just fine. However, if the referenced worksheet is "sheet-1" then the generated hyperlink doesn't work.

Sub IndexList()
Dim objSheet As Object
Dim intRow As Integer
Dim strCol As Integer

Set objSheet = Excel.Sheets
intRow = ActiveCell.Row 'Start writing in active row
strCol = ActiveCell.Column 'Start writing in active column

For Each objSheet In ActiveWorkbook.Sheets
Cells(intRow, strCol).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
objSheet.Name & "!A1", TextToDisplay:=objSheet.Name
intRow = intRow + 1
Next
End Sub

View 7 Replies View Related

Custom Number Format - Automatically Enter Dashes At Appropriate Spot

May 7, 2014

I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."

To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.

If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.

Is this possible in Excel (without VBA).

I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.

View 2 Replies View Related

Custom Format Which Displayed "-"(Dashes Without Quotes) Insted Of "0"

Aug 30, 2009

i have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)

View 4 Replies View Related

If Text Of Cell In Range Matches Text Of Cell In Column - Match Formatting

Jul 5, 2012

Trying to create a button that, once clicked performs the following task:

Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.

If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.

Only 1 cell in the range will match 1 cell in the column

For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background

During click:
matches these cells
changes X4 so that it also has a yellow background

After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background

View 3 Replies View Related

Excel 2013 :: Check Cell For Text And Return Corresponding Text In Adjacent Cell?

Jan 28, 2014

I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).

What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.

View 9 Replies View Related

Select One Text Cell From Multiple Text Cells With Checkboxes Reproduce Into One Cell

Jan 24, 2013

I have four cells that contain text. All have connected check boxes with TRUE FALSE.

I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.

I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").

View 1 Replies View Related

Compare Text Cell With Numeric Cell And Output Text String

Mar 25, 2014

As per title, I am trying to compare a column of text cells which contain "Yes" or are empty and a columns of numbers. If they are "Yes" and "1" on the same row, I want to output an "OK" message. Excel seems happy with the following code but it does not work and returns an empty cell if the two conditions are true.

[Code] .....

View 6 Replies View Related

Copy Any Bold Text Within Cell Text String To Adjacent Cell?

Jul 31, 2014

Here's an interesting one:

A1 contains a text string which is both bold and unbold (
B1 is blank

I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.

Example:

A1
The sky is blue

Macro is run

B1
sky blue

View 11 Replies View Related

If Cell Contains Text - Copy Text From Another Cell Over A Range Of Cells

May 22, 2014

I have a list of text quotes in column A. I then have column B which will have a Y entered if the quote is used in a presentation. I currently have around 100 quotes.

On a separate sheet, I want to be able to effectively say in one cell - If column B has text in it, copy the quote from column A. But the difficult part is how do I make this happen so that I can have multiple quotes being pulled into one cell?

View 11 Replies View Related

Enter Text In One Cell If Specific Text Is Present In Another Cell

May 9, 2008

is there a formula that will find a word in a sentence written in a cell and if present then enter specif text in another cell? for example, if the word "Hotel" appears in the sentence in cell A5 then put in the text "Hotel and subsistence" in cell B5...

View 2 Replies View Related

If Cell Contains Certain Text And Certain Text Then Return Predefined Text?

Apr 10, 2013

I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.

For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol

So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.

I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")

I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.

View 6 Replies View Related

Count Cell If Cell Contains Text Followed By Space And More Text?

Aug 8, 2012

I am not sure if this is possible, but worth a shot. I am using a countif function to count a cells and I want it to count only if it has text, followed by a space then more text. I've been able to count the text only followed by a space, but cant figure in the more text part.

View 2 Replies View Related

Leave Text In A Cell When Same Cell Has No Text Input

Jul 8, 2013

I am trying to figure E.g. when A1 is empty i want it to equal a text.

A1 has a text "Quote No" in it permanently until other text is put in

Purpose so that they people know where to enter the applicable text.

View 9 Replies View Related

Highlight Text Within Cell Against Equal Text In Another Cell

Jan 28, 2013

I have in Column A (quantity varies A1....An) a list of strings (i.e. file names NameX.txt and in column C I have a long string that contains many file names separated by ; that I extracted from the file name in Column A. what I want to do is to search column C individual text (file names) against the file names in column A. If the file exist in column A, then color only that text (file name) in column C. and so on.

See attached of an example of expected results.
Diery_exc_test.txt

ada_long_xyz_0a1;never_check_hello_2a1;testing_test_te_0a1;name_whatever_done_2a1

tesing1_abc_testy_1a2.txt

filename_xyz_test_1b2;diery_exc_test;Diery_hello_testy_1a2;abc_def_gah_2A1

[Code] ............

View 2 Replies View Related

Text To Row Instead Of Text To Column Using Comma Separated Values And Associate Cell To The Left

May 16, 2013

Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?

The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.

I have over 600 rows that needs this done.

1,2,3,4
B

[Code]....

View 3 Replies View Related

Make Text Flow Into Adjacent Cell Without Merging Cells Or Wrapping Text?

Jul 19, 2014

I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.

Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.

It is non-numeric text, and i've pasted --> values to remove formula issues.

View 1 Replies View Related

Automatic Update Of Cell Text Based On Item Number (also Text)?

Mar 28, 2014

I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.

Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.

See attachment : Example for forum.xlsx

View 3 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related

Auto-populate Cells To Display Text From Another Cell Without Cutting Off Text?

Sep 2, 2013

I am trying to auto-populate text in cells in area A, based on data I enter into other cells in area B. I want the area A cells to be for display only, as all editing will be done in area B. The problem is: how do I do this such that the text I write does not get cut off if is longer than the column width? See the attached document for a clear example and description of what I am trying to do...

Excel Q.xlsx‎

View 7 Replies View Related

Convert Array Into Text And Remove Duplicate Entries From Text In Cell?

Jun 12, 2014

I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.

Company 2Country B
Company 2Country C
Company 3Country C
Company 3Country C
Company 5Country A
Company 5Country C
Company 5Country C

For example:
- For Company 2, a cell containing "Country B, Country C"
- For Company 3, a cell containing "Country C"
- For Company 5, a cell containing "Country A, Country C"

I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.

The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.

There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.

View 3 Replies View Related

Search Cell For Text String And Paste Text In Based On Finding?

Jun 29, 2014

I need to create a macro to do the following:

Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.

For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.

View 7 Replies View Related

Show Cell Value (text) In Comment Box Text Or Mouse Tool Tip On Gif Icon

Nov 16, 2007

I have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value.

View 14 Replies View Related

Show Cell Value(text) In Comment Box Text, Or Mouse Tool Tip On An Gif Icon

Nov 16, 2007

I have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value.

I've look the properties of this to objects and can figure it out.

View 10 Replies View Related

Text Trimming - Display Part Of Cells Text Value Inside Another Cell

Jun 15, 2014

I want to know how to display part of a cells text value, inside another cell.

Suppose in cell A1 i have "20-Jun-14"

How would I get cell B2 to display just "Jun"?

View 4 Replies View Related

Macro To Automatically Convert PrtScr Image Based Text To Real Text In Cell

Mar 22, 2014

Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.

the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.

View 8 Replies View Related

Excel 2003 :: Take Specific Text From Cell And Copy It In New One (Text And / Or Words Position Varies)

Jan 17, 2013

I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.

Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)

Useful information are included !!!

I'll give an example of a record...

Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "

Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...

Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:

Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function

Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...

View 9 Replies View Related

Compare Cell Text To Set List And Show Text Differences

Dec 5, 2009

I am trying to write a function that will compare the text of cell "A2" to the text of cell "B2" and display cell "A2"'s text characters that didnt match from cell "B2" in column C. Can anyone help me out with writing this formula?

EX: ....

View 9 Replies View Related

Text Search Returns Cell Text Contents Of Different Column In The Same Row

Jun 7, 2007

Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found.

A prepopulated worksheet has the text "gold" entered in cell T278.

1. user searches for "yellow_metal"
2. Excell finds "yellow_metal" in row 278, say in cell A278.
3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example.
4. Excell returns "gold"

View 9 Replies View Related

Write Ranges To Text Files & Save Each As Cell Text

Sep 17, 2007

I m trying to write a macro which could take the text from a single column row T2 to row T313 and write it to a .txt file. Have the .txt file name created by the text in T4 or I could also put the text to name the file in T1 if you think it would be easier.

Then carry on to the next named sheet and produce another .txt file in exactly the same way until all 15 sheets have been completed. It would also be helpful if prior to starting to write each text files, it could test for any text in cell A2 of the sheet. The first empty A2 cell of a sheet would determine the end of the run, if it was prior to sheet 15 being reached.

View 2 Replies View Related

Combine Cell Content With Text File & Save As Text

Feb 21, 2008

- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.

I need to writing a VBA code for each of above (integrating text & cells, saving results as text)

View 5 Replies View Related

Specific Text In Column And Then Imputing That Text In A Cell?

Feb 3, 2014

So I have a column that will have the same text in all of the cells contained within it except for one cell. Is there a formula that I can use that will automatically find and transpose the text of the one cell that is unique out of the column? I was thinking of an IF function, but the column could be up to 60 cells in length.

View 1 Replies View Related

ADVERTISEMENT