Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-13-2020, 01:45 PM
DJ0691 DJ0691 is offline Search a row for cell formatted as currency Windows 10 Search a row for cell formatted as currency Office 2013
Novice
Search a row for cell formatted as currency
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default Search a row for cell formatted as currency

I'm looking for a method or formula to return a value from within a row if a cell within that row is formatted as currency. The currency cell is not in the same column for each row and there are multiple numbers in the row that are not currency. Also the currency cell itself does not actually contain a currency symbol like "$" although it is displayed that way.

I've tried =MATCH("$*",A7:S7,0) and that does not work as, like I said, there is not really a currency symbol in any of the cells.



Any ideas?
Reply With Quote
  #2  
Old 02-18-2020, 06:03 PM
BobBridges's Avatar
BobBridges BobBridges is offline Search a row for cell formatted as currency Windows 7 64bit Search a row for cell formatted as currency Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Interesting problem. I think I know how I would do it in VBA, but I looked at the options under Excel Find and didn't see anything likely. Seems to me you may have to write your own Excel worksheet function for it.
Reply With Quote
  #3  
Old 02-19-2020, 05:21 AM
ProudLiberal's Avatar
ProudLiberal ProudLiberal is offline Search a row for cell formatted as currency Windows 10 Search a row for cell formatted as currency Office 2019
Novice
 
Join Date: Jun 2017
Location: suburban Chicago
Posts: 28
ProudLiberal is on a distinguished road
Default

Try cell("type",A1), cells formatted currency will return a format code starting with C.
Reply With Quote
  #4  
Old 02-19-2020, 07:24 AM
ArviLaanemets ArviLaanemets is offline Search a row for cell formatted as currency Windows 8 Search a row for cell formatted as currency Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

formula like =CELL("type",A2) returns "b" for any cell format for me. But =CELL("format",A2) returns depending on format of A2:
General - "G";
Number - ",0" or ",2", etc.;
Currency - "C0" or "C2", etc.;
Date - "D1" or "G";
Percentage - "P0" or "P2", etc;
Text - "G".
Reply With Quote
  #5  
Old 02-19-2020, 09:10 AM
DJ0691 DJ0691 is offline Search a row for cell formatted as currency Windows 10 Search a row for cell formatted as currency Office 2013
Novice
Search a row for cell formatted as currency
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default

Thanks for the replies and appreciate the efforts. The CELL function appears to work for only a single cell, not a range or array like I need. I'll go the VBA route for a solution.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Currency Field - Displaying Incorrect Value when Formatted DarrenE Mail Merge 11 03-20-2019 01:40 PM
Search a row for cell formatted as currency Search Range Within Cell or Cells COEngineer Excel 1 06-01-2016 11:50 AM
Search a row for cell formatted as currency Search the text in 3rd cell by using texts in 2 cells Shinaj Excel Programming 1 05-09-2014 09:17 AM
Search cell text from Excel into ms-word ShailShin Word VBA 1 04-21-2014 02:12 AM
Search a row for cell formatted as currency search a list while typing in a cell virsojour Excel 1 07-08-2010 03:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft